OK, I've been watching my DB grow and have known for a long time I
would have to move the back end, but of course I waited until I
started getting odd behavior (at about 1.92GB). So today I installed
MySQL and (after a bit too much time getting the data source working)
started exporting tables. I successfully exported eight of my nine
tables, and can view them from a linked Access front end.
But when I tried to export the final table, which is probably about
90% of the space in the DB, it chugged away for an hour or so and
finally told me "not enough space on temporary disk". Well of course
this is nonsense (there's over 200GB free space), and the JETxxx.tmp
file is almost 2GB, so I gather the real problem is that JET isn't
sending directly to the ODBC interface but rather is constructing a
temporary file, and that even the temporary file has a 2GB limit.
And this just when life seemed like it might be easy.
What's the easiest way around this? I'm sure I could use various forms
of export to flat files, selecting a subset of the table, and import
these into MySQL. That just seems so clumsy compared with the ODBC
export, which although it requires several non-obvious clicks for each
table, once I learned what those clicks were, it seems to have just
worked, though I expect I'll have to learn a bit about MySQL to clean
stuff up.
I just noticed that I can export query via ODBC. Is that going to
create a table? If so, will that table get all the properties that an
export from a table gets? This could be the easiest (just set up a
query which selects part of the table, and run it for each section),
but my operational capability on MySQL is yet very limited and so I'm
concerned that I might find something very important missing after
I've put several hours of work into it.
This is in Access 2003 under XP, if it matters.
Thanks,
Edward