If you are using delphi, you first delete all the contents of the MySql table:
Delete from MySql.Table;
Then you can use a SELECT SQL from the Access Database and use the batchmove component to
add all records into the MySql Table. This is fine for tables that are not very large
and 25,000 records is not very large. For Large databases like 1,000,000 records, this
process can take quite a long time. For these cases, I suggest you create a delimeted
text file and use the command
LOAD DATA INFILE text_'filename_and_path' INTO TABLE XXXXXX. This is much faster but you
have to create the text file first.
Regards
Jose Ostos
----------
De: Alain Fontaine[SMTP:alain@stripped]
Enviado: Martes 16 de Noviembre de 1999 9:49 AM
Para: Anders Engstrom; win32@stripped
Asunto: RE: Updating Mysql-database. Advices, hints?
Hello,
You said:
[
I *think* I understand what you suggest:
-for each record in the access table
+delete the corresponding one in the mysql-table
+insert the access-record into the mysql-table.
]
Yes, that's it... more:
[
I need to entirely replace the old mysql
table with a new one (the access-table) - I can't leave any "old"
records in the new table. I guess this can be done by first wiping out
every record in the mysql table and the do insert's from Access (using
ODBC)?
]
That's what I meant with "delete & insert" pairs of queries. First, create a
query that will delete all records in your existing mySQL database, using
ODBC. Once you're done, run an insert query to insert your news records into
the tables. That's a "delete & insert query pair" ;))
To make it even easier, once your two queries work, create a macro that runs
both, one after the other, and presto. Couldn't be easier for someone who
doesn't know a lot about Access or databases, just push on a button that
says "Update" and there it goes. That's what I did in an application that
manages products and prices. When the user clicks on "Export to internet",
Access deletes the mySQL data using a delete query and ODBC, and immediately
afterwards inserts new data using an insert query and ODBC.
Keep me informed ! ;)
-----Message d'origine-----
De : Anders Engstrom [mailto:anders.engstrom.142@stripped]
Envoyé : mardi 16 novembre 1999 16:38
À : Alain Fontaine; win32@stripped
Objet : RE: Updating Mysql-database. Advices, hints?
Hi Alain.
No - I believe you got my question right <smile>.
I *think* I understand what you suggest:
-for each record in the access table
+delete the corresponding one in the mysql-table
+insert the access-record into the mysql-table.
Is that how you ment it? Anyway - this looks like a nice idea. But
(there is always a but :)
[I need to entirely replace the old mysql
table with a new one (the access-table) - I can't leave any "old"
records in the new table. I guess this can be done by first wiping out
every record in the mysql table and the do insert's from Access (using
ODBC)?
]
Let me know if I got _you_ wrong <smile>
Best Regards /Anders
=========================================================
On Tue, 16 Nov 1999 16:00:43 +0100, Alain Fontaine wrote:
Hello,
How about this: create a couple of update queries, or "delete and
insert"
pair queries, in Access, and use ODBC to directly update your mySQL
tables?
You shouldn't need to recreate indexes and "not null" stuff as the
mySQL
table definition and structure will never alter; the only thing that
will
alter is its contents.
Maybe I got your question wrong... ? ;)
>win32@stripped
--
Anders Engström
GIS-Centrum, Lund
0709-62 07 73
=============================
Your mind's like an umbrella.
It doesn't work unless you open it.
/Frank Zappa
---------------------------------------------------------------------
Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
posting. To request this thread, e-mail win32-thread960@stripped
To unsubscribe, send a message to the address shown in the
List-Unsubscribe header of this message. If you cannot see it,
e-mail win32-unsubscribe@stripped instead.
