Hi!!
I want to transfer a table from access 2003 to an oracle table.
i tried this code "DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DSN=tttt;DBQ=dddd;DATABASE=bbbb", acTable, "TEST"", but it doesn't work. it retrieves me an run time error 2507 and says that i don't have the ODBC databases type installed!
i can do this from clicking with the right bottom on the table name export>ODBC databases>.. but i need to do it from a form!
is it possible to open this export window using vba code?
Thanks a lot

I think you want "DoCmd.TransferDatabase acExport...

HTH


mmcdonal

i tried but it doesn't work as well..

After rereading your first post, perhaps you want to create a button on a form (don't use the button wizard) and put this code on the button.

Perhaps if you browse MSDN online for this issue...

I also found this post:
Dim db As DATABASE
Dim rs As Recordset
Dim retval As Variant

Dim DSNName As String, DSNUser As String, DSNPassword As String
retval = SysCmd(1, "Connection Oracle...", 16)

DSNName = "" 'fill those 3 next lines
DSNUser = ""
DSNPassword = ""


On Error GoTo Event_Err

retval = SysCmd(2, 4)
DoCmd.TransferDatabase acLink, "ODBC", "ODBC;DSN=" & DSNName & ";UID=" & DSNUser & ";PWD=" & DSNPassword & ";DBQ=edr1;DBA=W;APA=T;FEN=T;QTO=T;FRC=10;FDL=10;L OB=T;RST=T;FRL=F;MTS=T;CSR=F;PFC=10;TLO=0;;TABLE=C RJDACTDATA.TBL_EXPORT", acTable, "CRJDACTDATA.TBL_EXPORT", "Test1", False
DoCmd.DeleteObject acTable, "Test1"
retval = SysCmd(2, 16)
retval = SysCmd(3)

Event_Exit:
Exit Function

Does this help?


mmcdonal

Its almost done!!
i can export the table now but i can do it just once!! at the second time i got the error 3146 but if i change the oracle table name i can do it!
Is it possible to delete an oracle table from access using vba?
thanks

It makes sense that you can do it only once since you are creating a table. How about doing an append query instead?


mmcdonal

i tried as well but i'm not able to link the query with the oracle database!!
any suggestion?

thanks for the help

DoCmd.DeleteObject acTable... ?




mmcdonal

Sorry, I don't know what I was thinking.

Open a recordset on the table, and do...

rs.AddNew
...
rs.Update or UpdateBatch

Also, open a recordset on the table with the following sql statement:

DELETE * FROM tblYourTable"

Failing that, how about switching to SQL Server? I know it doesn't perform quite as well in some areas, but it costs 1,000 times less!

=)

mmcdonal

i solved the problem with an append query.

thanks for the help