acImport gives Linked table (Access2003)

Topic

New Reply

Hi!
I am using code to Import the table.
When it is linked – my query is very slow.
However it is making table Linked again.

Private Sub Form_Open(Cancel As Integer)

Dim msg As String
Dim rsp

msg = “Do you want to updat/refresh the data ?”
rsp = MsgBox(msg, vbYesNo, “Update Data”)

If rsp = vbYes Then

DoCmd.TransferDatabase acImport, “Microsoft Access”, _
“C:Documents and SettingsAccessReport.mdbs”, acTable, “ReportTable123”, “ReportTable”
End If
End Sub

How can I Import 6 tables?
And if you please, can explain it to me – why is the code works faster than when I am Importing same tables using File-Get Externat Data-Import???

THANKS

Reply | Quote

Replies

TransferDatabase with acImport does *not* link a table.
However, if the destination table “ReportTable” already exists, TransferDatabase does not overwrite it, but it imports to a new table ReportTable1 (or if that exists, ReportTable2, etc.)
So you should delete the destination table before importing the source table.

If you want to import 6 tables, issue 6 TransferDatabase commands.

VBA code generally works faster because it assumes that you know what you’re doing so it performs fewer checks than the menu option.

Reply | Quote

Call me crazy but I had added a line – DoCmd.DeleteObject acTable, “ReportTable”
and result is ReportTable appearing to be linked.
Though I have to say this database “C:Documents and SettingsAccessReport.mdbs”
is THE database where I am working. So maybe I should change it to CurrentDB or something?
And ReportTable123 is the Linked table from Oracle.

Private Sub Form_Open(Cancel As Integer)

Dim msg As String
Dim rsp

msg = “Do you want to updat/refresh the data ?”
rsp = MsgBox(msg, vbYesNo, “Update Data”)

If rsp = vbYes Then

DoCmd.DeleteObject acTable, “ReportTable”

DoCmd.TransferDatabase acImport, “Microsoft Access”, _
“C:Documents and SettingsAccessReport.mdbs”, acTable, “ReportTable123”, “ReportTable”
End If
End Sub

Thanks

Reply | Quote

Importing a linked table, whether from the same database or from another one, will result in a copy of the linked table, i.e. the result will be a linked table too

To import the data themselves you must import them directly from Oracle. The code will look similar to the followin, but with the correct names substituted, of course:

DoCmd.TransferDatabase acImport, “ODBC Database”, “ODBC;DSN=dsnname;UID=username;PWD=password;SERVER=servername”, acTable, “ReportTable123”, “ReportTable”

Reply | Quote

I am almost there…except I am confused with table names – “ReportTable123”, “ReportTable”

I am getting message that MS Jet DB engine can’t find ReportTable.
I had tried with ReportTable existed and ReportTable absent and absent and deleted.

Any suggestions? Thanks! It sees Oracle DB and it was the hardest part, right?
Name of THE table in Oracle I need to Import is ReportTable123.
ReportTable is destination, is it correct?

Reply | Quote

“ReportTable” is the name of the destination table in your Access database.

Are you sure that the error message is caused by DoCmd.TransferDatabase and not by DoCmd.DeleteObject?

Reply | Quote

Please, one more time. No, Delete code getrs passed fine.
It seems like my code is finding RecruitTable123 in Oracle and when it is getting to acTable, “RecruitTable123”, “RecruitTable”
it spits message that engine can not find RecruitTable. Error read ‘make sure object exists and name spelled correctly’…
I am not sure – if I should have a shell for RecruitTable? Does it suppose to exist as if it was Append query?

Or to make a story short – which table suppose to exist in Access and which is suppose to not before code execution? Thanks

Reply | Quote

The destination table should not exist yet. So I don’t understand the error message, sorry.

Reply | Quote

The destination table should not exist yet and ReportTable123 should/should not be linked into Access.mdb?
Sorry and thanks

Reply | Quote

The destination table should not exist yet.
It shouldn’t matter whether your database already contains a link to ReportTable123 or not, as far as I know.

Reply | Quote