Create tabels in MySQL from MsAccess frontend 1

[hermanlaksko]

I am using an access (2K) front and back end, but need to create/drop tables in MySQL for some data import/export.

I have the ODBC and connection and it works fine, I can connect via VBA code no problem, but some times I need to create new tables in MySQL, also via VBA/DAO code.

Does anyone have a solution to this.

Herman

They say that crime doesn't pay... does that mean my job is a crime?

 

[TonyGroves]

MySQL tables can be created using SQL statements.
Just connect to the server and send your CREATE TABLE statements.

See

http://dev.mysql.com/doc/mysql/en/create-table.html

 

[hermanlaksko]

Hi Tony
Thx for your reply, however I know how to create a table, but from a ms-access container, I am not sure how to create a tabel on a MySQL server.

I need to have the dbasename, password, user etc. I can use this:
DoCmd.TransferDatabase acExport, "ODBC Database", _
"ODBC;DSN=" & DSN & ";DB=" & DB & ";UID=" & UID & ";" _
& "PWD=" & PWD, acTable, Re!TabelName, "Q_" & Re!TabelNavn
AttachTable ConStr, "Q_" & Re!TabelName

This will create a table (image of a table in the access container) but the freedom of creating and droping a table in a CREATE TABLE-way is what I am looking for, can you help me here ?.
Thanks in advance.

Herman

They say that crime doesn't pay... does that mean my job is a crime?

 

[TonyGroves]

I'm afraid I'm not familiar with Access; maybe somebody else can help you. The Access forum might also be helpful.

 

[rzs0502]

How about using MyAccess?

http://www.hiden.org/myaccess/

"If you always do what you've always done, you will always be where you've always been."

 

[hermanlaksko]

Thx RZS
I will have a look at it.... but I hate addins , but one should allways allow oneself to try and learn something new.

Herman

They say that crime doesn't pay... does that mean my job is a crime?

 

[chris13]

Hi, there are multiple ways how the can be done from VBA-code. an example:

Dim cn As Connection
Dim wsp As Workspace
 
Set wsp = CreateWorkspace("NewODBCWorkspace", "admin", "", dbUseODBC)
 
Set cn = wsp.OpenConnection("connection1", dbDriverNoPrompt, , _
    "ODBC;DSN=My_ODBC_DSN;USR=User;PWD=Password")

cn.Execute ("CREATE TABLE TEST (test_id  integer, test_date date);")

You can store your SQL-code in querydefs and execute them from code, too. look for "QueryDefs", "CreateQueryDef", there are some good example in the online-help. Or ask here

 

[hermanlaksko]

Chris U R my hero THX man !

Herman

They say that crime doesn't pay... does that mean my job is a crime?