Exporting a Table to ODBC Database

[Bob Sanderson]

I'm trying to export a table to an ODBC database using a VBA subroutine. I
have tested the ODBC setup manually and it works fine but when I try to do
it with VBA, I get a "connection failed" error. This is the code I'm using.
Can anyone tell me what is wrong with it?

DoCmd.TransferDatabase acExport, "ODBC Database",
"ODBC;DSN=*****;UID=*****;LANGUAGE=us_english; " & "DATABASE=*****",
acTable, "Test", "Test"

Thanks in advance.

[Rich P]

What kind of database are your exporting to?

Is it a Sql server? Oracle? Sybase? a Mainframe?

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Bob Sanderson]

Rich P <rp*****@aol.comwrote in news:475d9c11$0$3575$815e3792
@news.qwest.net:

What kind of database are your exporting to?

Is it a Sql server? Oracle? Sybase? a Mainframe?

MySQL

[Rich P]

I am not as familiar with MySql as Sql Server, but I will offer some
suggestions.

First, when you say you want to export a table to your MySql DB I am
interpreting that as you want to write data from one table in an mdb to
a table in the Server DB. If this is the case and you have a working
ODBC connection to the MySql DB, I would try an append query.

If you have a good ODBC connection to the MySql DB, you should be able
to see the desired table in the Access Table window. If you can't see
the MySql table in the Access Table Window, you may not have a good ODBC
connection. I would check this first. Make sure you can see the table
from MySql in Access first. If you can, then try the append query.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Bob Sanderson]

Rich P <rp*****@aol.comwrote in news:475da4bc$0$3567$815e3792
@news.qwest.net:

I am not as familiar with MySql as Sql Server, but I will offer some
suggestions.

First, when you say you want to export a table to your MySql DB I am
interpreting that as you want to write data from one table in an mdb to
a table in the Server DB. If this is the case and you have a working
ODBC connection to the MySql DB, I would try an append query.

If you have a good ODBC connection to the MySql DB, you should be able
to see the desired table in the Access Table window. If you can't see
the MySql table in the Access Table Window, you may not have a good ODBC
connection. I would check this first. Make sure you can see the table
from MySql in Access first. If you can, then try the append query.

If I use the manual method (File, Export ...) I can select the ODBC
database and export the table without any problem. The only time I can't
connect is with the Visual Basic code.

[Rich P]

In the File menu (top left corner of Access) select

Get External Data

--then select

Linked Tables

When the Link dialog box comes up -- scroll to the bottom of the "Files
Of Type" drop down list. The last selection is "ODBC Databases".
Select that, and it will bring up another dialog with the List of ODBC
connections you have. Select the Machine Datasource tab and look at the
list of ODBC connections (.dsn). Select the dsn for the MySql
connection. If you have a valid ODBC connection to MySql this will
bring up yet another dialog box with a list of tables in the MySql DB
that you can connect to from Access. Scroll through that list and
select your table.

Note: Make sure to check the "Save Password" check box at the bottom
Right corner of this dialog or else you will get an real annoying
Password prompt everytime you try to open this linked table.

Try this and let us know how it works out.

Rich

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Bob Sanderson]

Rich P <rp*****@aol.comwrote in news:475daf4a$0$3580$815e3792
@news.qwest.net:

In the File menu (top left corner of Access) select

Get External Data

--then select

Linked Tables

When the Link dialog box comes up -- scroll to the bottom of the "Files
Of Type" drop down list. The last selection is "ODBC Databases".
Select that, and it will bring up another dialog with the List of ODBC
connections you have. Select the Machine Datasource tab and look at the
list of ODBC connections (.dsn). Select the dsn for the MySql
connection. If you have a valid ODBC connection to MySql this will
bring up yet another dialog box with a list of tables in the MySql DB
that you can connect to from Access. Scroll through that list and
select your table.

Yes, this works fine. It's just the VB code that doesn't work.

[Chuck Grimsby]

On 10 Dec 2007 17:23:02 GMT, Bob Sanderson
<ne**@LUVSPAMbobsanderson.comwrote:

>I'm trying to export a table to an ODBC database using a VBA subroutine. I
have tested the ODBC setup manually and it works fine but when I try to do
it with VBA, I get a "connection failed" error. This is the code I'm using.
Can anyone tell me what is wrong with it?

DoCmd.TransferDatabase acExport, "ODBC Database",
"ODBC;DSN=*****;UID=*****;LANGUAGE=us_english; " & "DATABASE=*****",
acTable, "Test", "Test"

You can't use TransferDatabase to send records to a ODBC Database.

Link to the ODBC table, then use a INSERT (Append) Query to transfer
the data from your Access database to the ODBC database.
---
Please Post Any Replies To This Message Back To the Newsgroup.
There are "Lurkers" around who can benefit by our exchange!

[Bob Sanderson]

Chuck Grimsby <c.*******@worldnet.att.net.invalidwrote in
news:pi********************************@4ax.com:

>You can't use TransferDatabase to send records to a ODBC Database.

I'm confused. Microsoft seems to think that you can. I got the code from
their help files.

Link to the ODBC table, then use a INSERT (Append) Query to transfer
the data from your Access database to the ODBC database.

I'm trying to upload a new table to the ODBC, not append to one.

[Chuck Grimsby]

On 13 Dec 2007 16:41:12 GMT, Bob Sanderson
<ne**@LUVSPAMbobsanderson.comwrote:

>Chuck Grimsby <c.*******@worldnet.att.net.invalidwrote in
news:pi********************************@4ax.com :

>>You can't use TransferDatabase to send records to a ODBC Database.

I'm confused. Microsoft seems to think that you can. I got the code from
their help files.

You mis-read the help file.

---
Please Post Any Replies To This Message Back To the Newsgroup.
There are "Lurkers" around who can benefit by our exchange!