By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,164 Members | 969 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,164 IT Pros & Developers. It's quick & easy.

Auto-linking in Access is it possible?

navyguy59
P: 2
I need to know if I make an Access database that utilizes the Get External Data function to draw data from multiple Excel documents, can I do something to auto-update the database to accept new excel documents saved into the target folder and incorporate them into the database or do I have to enter each new document manually? The excel documents I plan to link into the database are monthly reports with a new excel document added to the folder every month. Is there anything I can do to accomplish this?
Sep 6 '07 #1
Share this Question
Share on Google+
4 Replies


JConsulting
Expert 100+
P: 603
I need to know if I make an Access database that utilizes the Get External Data function to draw data from multiple Excel documents, can I do something to auto-update the database to accept new excel documents saved into the target folder and incorporate them into the database or do I have to enter each new document manually? The excel documents I plan to link into the database are monthly reports with a new excel document added to the folder every month. Is there anything I can do to accomplish this?

Is the layout of the spreadsheets always the same? Same columns and datatypes?

Do you wish to put everything into the same table each time?

What do you wish to do once a spreadsheet has been uploaded? Will you move it or rename it so it doesn't get imported twice?

will there be duplicate data from spreadsheet to spreadsheet?
Sep 7 '07 #2

navyguy59
P: 2
Is the layout of the spreadsheets always the same? Yes it the same layout just with new figures added to reflect fule amounts and time flying used for the month.
Same columns and datatypes? Yes

Do you wish to put everything into the same table each time? I want to be able to track 12 months worth of data on fuel used and time in flight basically. I will manipulate this data in various ways to be sure but on that note I am waiting for the Maint Officer to clarify what all he needs done with the information. The data will comprise of 12 excel documents one generated each month and placed into a shared drive where we can access them. They will come out with a new one each month.
What do you wish to do once a spreadsheet has been uploaded? The Officer I am working on this project for has not yet decided what all he needs this database to be able to do with the data. Until he can clarify that part for me I am just trying to get an outline of what I can do.
Will you move it or rename it so it doesn't get imported twice? The database or the excel files? Database should stay in the network folder I create it in, I do not forsee the files name changing. The excel documents will be named by month and year...Example: Jan07.xls
Will there be duplicate data from spreadsheet to spreadsheet?
Duplicate data will be rare as the fuel quantities from month to month and flight times are always changing as we fly at the needs of the Navy not at a set schedule.
Sep 7 '07 #3

JConsulting
Expert 100+
P: 603
Duplicate data will be rare as the fuel quantities from month to month and flight times are always changing as we fly at the needs of the Navy not at a set schedule.

Take a look through this function. It uses a table that stores the path to the spreadsheets and loops through the directory importing the files one after another. It may be a bit much for what you're after, considering you'll have one per month, but the method is still valid.

Expand|Select|Wrap|Line Numbers
  1. Public Function LoopThroughPath()
  2. Dim strPath As String
  3. Dim strFile As String
  4. Dim myFullFile As String
  5. Dim ImportTable As String
  6. Dim strDest As String
  7. ImportTable = "MyImportTable"
  8. 'Clear old records out of the Import Table
  9. CurrentDb.Execute "Delete * from " & ImportTable & ";"
  10. strPath = "C:\Test\"   ' Set the path for the Source Directory.
  11. strDest = "C:\Test\Imported\" ' Set the path for the Save Directory.
  12. strFile = Dir(strPath, vbDirectory) 'Don't change this part
  13. Do While strFile <> ""
  14.     If Right(strFile, 4) = ".xls" Then ' Criteria, ie File Extension
  15.         myFullFile = strPath & strFile
  16.         'Import data to the Import table
  17.         DoCmd.TransferSpreadsheet acImport, 8, strTable, myFullFile, True, ""
  18.         'Now I want to move the selected excel file to the "done" directory
  19.         FileCopy myFullFile, strDest & strFile
  20.         Kill myFullFile  'Delete the spreadsheet from the Source Directory
  21.     End If
  22.     strFile = Dir()
  23. Loop
  24. End Function
  25.  
J
Sep 8 '07 #4

P: 31
Can this code be used to import .dbf files also? I am trying to import only the new .dbf files that are generated. The file all start the same but increment in numbers.
Sep 24 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.