Structure for mysql database with access front-end

I have a server that collects a lot of information. Right now there is a database for each month and a table for each day of the month. Usually there are about 200,000+ rows per day (about 150MB a table).

On another post, someone suggested I just use one table per month to make it easier to manipulate the data. This would not be hard since all the entries have a date\time stamp.

The problem is that we ha开发者_StackOverflow社区ve to use an MS Access front-end and the data is not linked, but imported each day for revision. This has worked well with a VBA script that automatically connects to the right database & table.

sConnect = "ODBC;Driver={MySQL ODBC 5.1 Driver};" & _
  "SERVER=172.16.0.0;" & _
  "PORT=3306;" & _
  "DATABASE=" & "Mon" & dbMonth & ";" & _
  "USER=USER;" & _
  "PASSWORD=PASS;"

DoCmd.TransferDatabase acImport, "ODBC Database", _
  sConnect, acTable, "Data" & dbDay, "Revise", , True

Anyone have a suggestion for an quick & easy way to automatically import just the day's info from a database that has 6 million+ entries? Right now it takes about 20 seconds over the LAN to import one day's worth of data.

Thanks!

One question that enters my mind is why are you separating your information off like this - why not simply time stamp the entries, and dump it all into one database to work against. Much easier to keep track of, maintain, backup, and restore - then just write your VBA to fetch the appropriate records for your front-end to revise, pushing them back to the table within that one database once your revisions are through - perhaps checking a 'Revised' checkbox to indicate that these should NOT be pulled next time (if I am understanding the purpose correctly).

Another is why would you not want to link to the database, or at least a view of the database?

I am still a little new here, so perhaps there is larger concerns I am not seeing with my questions - and if so, I'd love to be enlightened as well.

I'd stick with one table in MySQL and add a timestamp field with a default of CURRENT_TIMESTAMP, so that MySQL will auto-populate this timestamp field.

A simple query can then return one month's records, which you could append to a temp (?) table.

However...

we need to have the data imported into a table and not just linked

Are you sure you really need to do that? If you are processing each record further via vba, can you not include any additional info as extra fields in the original MySQL table?

I would put all data into a single table, create a link instead of importing and generate your query from VBA. Yes, it may be a bit complicated up front, but, in my opinion, the easiest to continue working with and maintain.

We have mixins of MySql, Access database, Access forms, VB6 and VB.Net. It is a pain to work with all of these technologies. I have done enough with the apps to know that a single table and re-linking is your best bet.

Well, I am going to restructure the database, because I found out that Access has a "hidden" way to populate the filtering list from linked tables. In database options you can tell it to use ODBC for filter lists too. It is was hard to find & I didn't even think it could be done. Now I have a lot more options because I can just link the tables.

Thank for all those who tried to help.