What is the problem with using the linked FoxPro tables until you are ready to retire the system?
My goal was set to make sure the database I create can handle all the data it is going to need (a management system). My bosses first desire was to make sure I could retrieve the data from the foxpro tables as previous attempts have failed.
I am a placement student working on this task, so I am only here for a year, and I'm 5 months into that. He told me that he's had people who tried linking tables before I arrived, and it didn't work satisfactory for him. The system I have now, whilst yes it does require regular compacts, performs in a way that works for my boss - the client - the overall aim in any task.
I just have to make sure the users know to not try opening the database for a few seconds after closing it, whilst it completes the compact (the database window is hidden and you can't see the compact progress bar).
Also the foxpro system they are using is not very user friendly. Timesheet information (making most of the daily input) has to be typed into it, from printout of excel spreadsheets that could be set-up to transfer the information straight into an Access table. All the information is effectively being double typed, and it is a waste. The company partner has decided rather than trying to upgrade the foxpro in someway, to go this way instead. He therefore wants to see Access handling everything it can by itself, before the Foxpro is retired.
Sounds great, can you explain in a little more detail as to how you set the tables up??
Calvins advice for the maketables is what I did. As Pat mentioned, I'm not actually linking to the tables, rather importing them. Once I have them, some I can use as they are (therefore not requiring any setup), whilst a couple of them do require some extra work.
A macro renames the old table, then a maketable query sorts out the relevant records from the table and strips them out into a new table. This is given the original name of the old table and the old table is deleted. Your left with an identically named table, with just the records required.
The renaming, the maketable query, and the deletion can all be within one macro that takes 5-10 seconds to run on a table with (currently) 42000+ records.
You must make sure to compact the database after a major importing, as the signifcant database bloat that Pat mentioned is very large. A 26mb database can bloat to over 100mb in 2-3 runs through the importing process. Compacting sorts this out though.
If you have more questions keep pestering me as I'm not sure what else you need to know (I don't usually help people, it's always people helping me, but as my experience grows hopefully I can hit a nice balance).
Cheers
