Automate Importing w/separate source tables

[bren11]

Hello,
I am just wondering if it's possible to do either of these two things?
1. Automate an import when your source tables are all separate, such as with FoxPro tables...or
2. Have the import be manual, but append the (newest)data automatically into a master table. The file names of the source tables increment numerically, so I can't have a pre-set-up query...

If anyone has any suggestions, it would be greatly appreciated!

 

[GDGarth]

I do something similar, I think.

I've got a database that dumps test data into a database from several testers. Each tester is running different products.

In my case, the name of the record file is the name of the asssembly, so I examine the directory, store the name of the file in a variable, then change the name of the file to "Testit".

I have a linked table that references Testit, so all I have to do is run a query to add the records from Testit into the main database table.

Then I rename (in my case delete) the file, and move on to the next one.

Here's the steps:
Create a linked table to a standard name.
One by one, change each file name to your standard name.
Run an append query to add the records to the table.
Delete or rename the file.

My database has been running for two years, updating once a minute. It works pretty well.

 

[bren11]

GDGarth,

Thanks for the direction. I have a clearer idea now of how to proceed based on your suggestion. Thank you for your time.

 

[cchanslor]

GDGarth's approach is good - I use similar for some of my applications.

Another approach I use (alluded to in your first option in your post) is to have my Access code create my connections and do my append queries (which I hard code). I pick up several files that I upload to tables - I get these daily, weekly, monthly - each has a different date stamp signature. I have a GetDate function that returns the appropriate date signature for the files I am processing then it creates the link to the table within the file. The code runs the append query then removes the link and deletes the file. I imagine you could do the same type of thing with numerically incremented files. I would use FileSearch function to get a list of your db files, then step thru the list, create links, append, then clean up.

This probably all boils down to personal preference. I use so many different, large tables and use many, many queries. If I kept every link (or even all tables in a single file) and every query my file sizes would be huge. I do as much as I can via table-driven code. I run all code from a single file. I prefer to keep this db I run my code from clean - I have basic tables for file location info, etc and do everything else "virtually" (make connection, do work, end connection).

Anyway, just my 2 cents.

 

[bren11]

This is great...a GetDate function as you mentioned is exactly what would make this functional, in that the user is only interested in doing processes based on the create date of the file....I VERY much appreciate your input,and your taking time to post.

Thanks!