Hi,
I have several dbf files that I imported into Access in order to establish relationships and export as spreadsheets. I have created a macro that does that. However, the dbf files are based on a query that changes monthly. I was wondering if there was any way to automatically update the tables from the dbf files once the queries are run without manually importing the tables again?
Thank you for any assistance.
I have MDB database linked to SQL SERVER through VPN connection.I created links to the sql server Links are dsnless..Everything works fine but when I lost VPN connection or sql connection has been broken I can't refresh links to the tables.I receive message 3146 sql connection failed..I must close database and start again...
I tried different methods like ado,dao, and vba docmd.transferdatabase,aclink... but no success, table cant be relinked.
Only way I can relink is to change ip adress in conn.string
E.g. 192.124.0.2 (1st ip- router server ip) and after connection failed i can use 192.124.0.32 (2nd ip - server local ip) and that's it if i lost connection for the 3rd time... i must restart application.
It seems that access database keep the previous connection..how to reset or drop database connection to the sql server and refresh links to the tables with vba code without closing access database...
Can you import from a .txt file on Notepad into an Access table or will it have to go via an excel spreadsheet to begin with?
View 1 Replies View Related2013 does NOT recognize DBF files, we are trying to find a solid work around to LINK to our DBF files.
There are about 30 DBF files that get updated EVERY week. And there are about 60 employees that LINK to the various DBF files. Importing is not a realistic solution given these circumstances.
Running Office 365 (Access 2013). Recent problem occurrence. When I do an 'External Data' command, either from another Access database or an Excel file, the import completes, but I don't see the new table immediately in the navigator section. If I import from another Access database, the new imported tables appear as '~TMPCLP....' tables. If it's from an Excel workbook, the new table doesn't appear at all. It's only after I exit the database and reopen it do I see the new tables correctly named.
View 1 Replies View RelatedI have a client who needs a Database created in Access for him. he would like to regularly auto-populate this Access DB from an Excel file - like twice a week.In addition, he wants me to create Forms, Queries, reports and Macros for him in the Access, which will have 2 Tables from Excel files and about 3 Tables created in Access.I figured that I could do that if I gave him a link to the Tables he needed from the Excel file(s). But how do I work remotely for him- the path to those files is on his computer. I can load them to mine, but then he can't open the files.How can I create Forms etc. in an Access DB which uses links from source files as its Tables?
View 2 Replies View RelatedI have linked tables from SQL Server using ODBC connection that their location never changes. I have used certain fields of those tables to create queries and make table queries to derive to the information I needed.
On these tables on SQL Server, there is new data added daily. Every day, midnight, there's new data records added of whatever transactions took place in that working day. how often do I need to refresh linked tables in this case to get the latest data added. I mean, once I am linked, the make table query using those defined fields, would it get the latest data added by default when the query is executed, or I must refresh linked tables using Linked Table Manager and then run make table query.
Also, if I want the access to automatically refresh linked tables, can I use the following code? I have added this code, and executing it through a button, but I don't see anything happen, the database becomes inactive for couple seconds (I guess while it is updating) but I don't know is it updating the tables for sure or not, though I am not receiving any error when executing the code through the button.
Function RefreshLinkedTables()
Dim tdf As TableDef
For Each tdf In CurrentDb.TableDefs
If Len(tdf.Connect) > 0 Then
tdf.RefreshLink
End If
Next td
End Function
Hi all
I need some code to refresh/update linked tables to a data base in the same folder on startup. Any idea how I can do this??
Thanks
Damo
I have an access file which connects to an ODBC database, and I want it to update at a set time interval.
I searched through the internet, and noticed that most solutions require a form and putting the requery macro in the form's events.
My file doesn't need a form, its only function is to act as a medium to store the data from ODBC and lets a BI Tool play around with it.
My question is, how do I make the access file update automatically at a timely interval (every 30 mins, 1 hr etc.). Is it necessary to create a form?
I found 2 examples of code to refresh my attached table link to sharepoint lists.
I have a scheduled task open my Access 2010 db and an autoexec macro runs and closes the db.
My problem is my tables disconnect from sharepoint 2010 and the update fails.
I added the code I found to a module and added a line in my macro to run the code. Now I cannot get the code to work.
Am I at least on the right track? I just want to know before I spend many hours getting this code to work.
These are the two locations of code I found.
[URL]
I think part of my problem is that I have a regular table that I add a txt file to that is not linked to sharepoint so I need to exclude it from the code.
Hello All,
I have a bit of a problem. I have a main form with two subforms. The subforms are linked to the main form my two fields. The main form and subforms are each based upon a different query.
I have written VBA code that filters the SQL statements of the subforms based upon the value of the the REV field on the main form. When the user changes to different record on the main form, the subforms are supposed to change accordingly.
The problem is that the subforms on the main form will not refresh each time the user chooses a new record on the main form. If I change to a new record on the main form and then open the subform outside of the main form, the correct information is shown. The problem however is that the subform shown on the main form does not automatically refresh. I have tried numerous strategies, but to no avail. If I am on a record in the main form, and I exit the main form, and then re-open the main form, the subform data on the main form will reflect that of the record I was on prior to previously exiting the main form.
Here is some of the code that I have tried:
Me![qryRevText_ sf].Form.Requery
Me![qryAdminRevText_sf].Form.Requery
Me.Refresh
or
Me.[qryRevText subform].Form.Refresh
Me.[qryAdminRevText subform].Form.Refresh
or
DoCmd.Close acForm, "qryRevText subform"
DoCmd.Close acForm, "qryAdminRevText subform"
Please help me if you can. I would appreciate it greatly.
Thank you in advance.
Akagami
I'm trying to get one database to be shared by multiple users so that the file on the server is always updated as others add new records....in real time. Is there a way for this to work? What settings will I need?
View 2 Replies View RelatedAs part of my job I am constantly comparing files from a production environment against those from a UAT environment. The checks are generally the generic before I reconcile the monetary values I check that the static data population is the same. Basically I import the files into access and then do a like for like comparison on the number of blanks per field. Eg.
Field Blanks_UAT Blanks_Pro Diff
Coustomer_Number 10 2 8
Is there any way of doing this programmable so I can reuse it or are there any tools out there that do this work for me. Some of the files contain a large number of fields so the work can become rather cumbersome!
Any help is appreciated
Hello Access giggks,
I here attached Acess db and Excel file shows my db structures for you to see and be able to help me out. All I want to do is either to create a command or query to import file from two different tables which have parent-child relationship from a different folder to my current db of similar tables (except for different field names), which is the main data storage. I, therfore; ask you to help me out resolve this issue. I know that someone offered me an help last week but asked me to give him/her more information. Here they all are attached to this email except for some field names. I omited them out to reduce the amount of data to the limited amount. Thanks for your help.
Regards,
Girma
I am developing a 2010 database which is split. In the back end I am linking to csv files - with a new file every month to link to.
What I would like to do (and have not been able to find a way to do at all so far! ) is each month to add the new table link in the _be and to be able to choose it in the front end user interface to view reports on, without having to "re-programme" the underlying queries to use the new linked table.
I am importing a field from and old DOS based data base where dates and text are mixed in the same field. The field can have either one of more dates or text. That is the possible entries in the field are:
(1) 12/12/2013
(2) 12/12/2013, 12/1/2013 or some other delimiter other than a comma
(3) 6 CDs
When I tried to import the field, I am finding that (1) results in a string of numbers which represents the date. The others are, of course, OK.What I am trying to figure out is if the first type occurs and I have 41640 it will appear as 01/01/2014. So, can you have mixed field types in the same field by using some custom formatting with an if (of Iif) statement and what would that be? Something like IIF(IsDate([FieldA], format([FieldA], short date), @) I just cant seem to get the right nomenclature or number of correct arguments.
I am building a database for a coworker. For one table, he'd like to extract data from different companies' financial reports, specifically a Schedule RC-B (schedule of securities). The format it comes in is PDF, but there are options to download XBRL, SDF and Taxonomy. My computer doesn't recognize any of these other formats. Plus, I'm guessing each company will have a slightly different format, so I don't know if there'd be an efficient way to set up an automatic import. Any way for importing/extracting data from these files?
View 2 Replies View RelatedHi:
The company I am consulting with uses Access 2000 and has no intentions of upgrading. I currently do not own Access on my laptop.
I was planning to purchase Access 2003 for my laptop but am concerned that files I create in 2003 might not be "editable" in 2000 even if I choose the 2000 format when creating new databases.
I'm just wondering if anyone has tried creating a file in 2003 (using the 2000 format - as I'm told you can choose what format you want to use) and then edited it in 2000. I want my client to be able to edit the files in 2000 once I've moved on. (I can get 2003 for about the same price as 2000 - so my preference would be to get 2003.) The other obvious reason for going for 2003 is that I could run into the reverse challenge if I own 2000 and my next client uses 2003. At that point, I'd be forced to upgrade to 2003.
My databases are not complex - but do include some customized macros.
Any help, comments... appreciated.
Thx,
dave
I'm having a number of tables in Access which I want to populate with data from excel/txt. In my import files I have some columns which contain alphanumeric data, like 0001 or XYZ0.
I've defined these columns as text in the definition of my table, however whenever I'm importing the text file, it converts those rows that can be read as a number to a number prior to storing it in my table. Hence values like 0001 becomes 1. Which is absolutely not what I need.
How I can prevent Access from doing this conversion? Why does it do the conversion anyway? I wouldn't expect any conversion since I defined the column as text...
When I import the same txt file into a new table (created during import, where I also define those columns as text) everything goes fine, 0001 stays 0001, but I don't want to remove my table and redefine it during the import everytime I need to import new data.
I have a split accdb in Access 2010 with tables/data in the back end file (stored on a network drive) and the forms, queries and code in a front end file stored on each desktop. Note the front end file is common to two back end files. Both back ends will be mirrored but I need to use one for live data and the other for development and testing.
I want to be able to include vba code when the front end is launched that prompts the user to choose between one of two back end files, one 'test' and the other 'live'. But I've spent all afternoon scouring the net with no success on something simple but effective.
As I see it I just need an option on a form and depending on which is selected the following happens:
- links to current/existing tables are deleted
- chosen back end file (test or live) is selected (this has a password)
- links to the tables of the chosen file are created in the front end
- the appropriate back end source is now working with my front end
Access 2010 doesn't seem to like the last line. It gives me an error saying the form name doesn't following access object naming rules.
Sub update_subform()
Dim dbsCurrent As Database
Dim qryD As QueryDef
Dim strSQL1 As String, strSQL2 As String, strSQL3 As String
Dim mytmp As String
Dim proc As String, myot As String
[Code] .....
Ive tried making command buttons for each of the three tables that when you click on it, it automatically updates the tables, but it doesnt seem to be updating them. Is this the right code for that to happen?
Private Sub Command0_Click()
Dim strFile As String
DoCmd.SetWarnings False
' Set file directory for files to be imported
strPath = "C:SharesPublicStaff Public FilesBrandon PenlandUS Food Product Prices Newest"
' Tell it to import all Excel files from the file directory
strFile = Dir(strPath & "*.xls*")
[code]....
Does anyone know that path to the folder that the dll files are stored in that Access uses?
View 3 Replies View RelatedI have to write a code for my database,i have folder with files "pending Review" and a table with column "tblExcelLocation". when i run my database all the files from pending review folder goes to "tblExcelLocation" on a click of button.But,if the files already exists it should not insert those files and insert the rest.For this i tried to write a code but i think i m unable to do that .
Code:
Loop through files in folder
folderspec = "O:QA FilesQC ReportingPending Review"
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(folderspec)
Set fc = f.files
[code]...
:confused: is ther anyway to lock ms access files , so that people would not be able to modify then and only view them, would not get to vbs screen etc, and give a limited password that will expire in given ammount of time.
View 1 Replies View RelatedHi, I’m trying to Import an external CSV file in to Access and then Update/Add the record into a table. I need to be able to do it using SQL and I’m not allowed to touch RecordSet!
Does anyone know How I could do this or where I would be able to find help on this.
Thanks
coops1204