DoCmd.TransferSpreadsheet problem in Access2003 (error 3078)

[Christophe Niel]

Hi

I'm having a problem with this command :

DoCmd.TransferSpreadsheet _
acImport, _
acSpreadsheetTypeExcel97, _
"dbo.myTable", _
"myXLfile.xls", _
True, _
"mytable1"

It works fine in Access2000, I have no error and the date are imported from
Excel to Access Successfully

When I open the same file with Access 2003, with or without converting it,
this command doesn't work anymore and I have this error (roughly translated,
it's not english):
Error 3078, the Jet database engine could not find the table
'dbo_myTable'. Make sure the name is correct.

If I use "[dbo].[myTable]" the error is '_dbo___myTable_'
I think there is either a bug, or I can't use a "." anymore, and that would
be a big problem, or there is a new way I don't know about to reference a
table.

Ho, by the way, I use an adp file, not a mdb, that's why my table need the
prefix "dbo." to be referenced correctly.

Thanks in advance for any information.

Best regards
Christphe Niel

 

[Van T. Dinh]

So what is the actual name you use for the linked Table (as seen in the
Tables tab of the Database Containers windows).

"dbo.myTable" would not be the normal name Access created when you linked
the Back-End Table. If you actually have "dbo.MyTable" as the name of the
linked Table, you need to use "[dbo.MyTable] since "dbo." is part of the
name and not a qualifier.

Are you aware that you can change the name of the linked Table without
affecting the link to the actual Table in the Back-End? I would certainly
change the name so that the name doesn't contain special symbols (and "." is
a very special symbol!).

 

[Christophe Niel]

Hi,

The actual name of the table is (remove the double quotes) "myTable
dbo)" - yes, with the space, the parenthesis and dbo in the parenthesis...

I did not link the table myself, I used a connection string at the creation
of PROJECT file (not a mdb, but ADP) and the tables, view and stored
procedures from a SQL are DIRECTLY used.
If i rename the table, the table is renamed in the database.

I did try the [dbo.myTable] solution, it creates a table named
"_dbo_mytable_" in the SQL database, in the user schema of my user, not the
dbo's user schema. (there is no more error though )

as for [dbo].[myTable].[myField], that is a valid SQL syntax (in SQL Server)
to ensure the "correctness" of the table name, allowing you to use reserved
word like [date],

, [field] in table name or field name.

Thanks for your reply.

Best regards,
Christophe Niel

So what is the actual name you use for the linked Table (as seen in the
Tables tab of the Database Containers windows).

"dbo.myTable" would not be the normal name Access created when you linked
the Back-End Table. If you actually have "dbo.MyTable" as the name of the
linked Table, you need to use "[dbo.MyTable] since "dbo." is part of the
name and not a qualifier.

Are you aware that you can change the name of the linked Table without
affecting the link to the actual Table in the Back-End? I would certainly
change the name so that the name doesn't contain special symbols (and "."
is
a very special symbol!).

--
HTH
Van T. Dinh
MVP (Access)

Hi

I'm having a problem with this command :

DoCmd.TransferSpreadsheet _
acImport, _
acSpreadsheetTypeExcel97, _
"dbo.myTable", _
"myXLfile.xls", _
True, _
"mytable1"

It works fine in Access2000, I have no error and the date are imported from
Excel to Access Successfully

When I open the same file with Access 2003, with or without converting
it,
this command doesn't work anymore and I have this error (roughly translated,
it's not english):
Error 3078, the Jet database engine could not find the table
'dbo_myTable'. Make sure the name is correct.

If I use "[dbo].[myTable]" the error is '_dbo___myTable_'
I think there is either a bug, or I can't use a "." anymore, and that would
be a big problem, or there is a new way I don't know about to reference a
table.

Ho, by the way, I use an adp file, not a mdb, that's why my table need
the
prefix "dbo." to be referenced correctly.

Thanks in advance for any information.

Best regards
Christphe Niel

 

[Van T. Dinh]

I don't use ADPs except for some minor testing so I am not sure about this
one.

Suggest you re-post in the "microsoft.public.access.adp.sqlserver"
newsgroup.

 

[Christophe Niel]

Thanks,
I did this and I'm now waiting for an answer, I'll post it here, if it
interests anyone.

bye

I don't use ADPs except for some minor testing so I am not sure about this
one.

Suggest you re-post in the "microsoft.public.access.adp.sqlserver"
newsgroup.

--
HTH
Van T. Dinh
MVP (Access)

Hi,

The actual name of the table is (remove the double quotes) "myTable
dbo)" - yes, with the space, the parenthesis and dbo in the parenthesis...

I did not link the table myself, I used a connection string at the creation
of PROJECT file (not a mdb, but ADP) and the tables, view and stored
procedures from a SQL are DIRECTLY used.
If i rename the table, the table is renamed in the database.

I did try the [dbo.myTable] solution, it creates a table named
"_dbo_mytable_" in the SQL database, in the user schema of my user, not the
dbo's user schema. (there is no more error though )

as for [dbo].[myTable].[myField], that is a valid SQL syntax (in SQL Server)
to ensure the "correctness" of the table name, allowing you to use reserved
word like [date],

, [field] in table name or field name.

Thanks for your reply.

Best regards,
Christophe Niel