Another Acc2000 vs Acc2003 problem : dbo.mytable = dbo_mytable ! (Error 3078)

[Christophe Niel]

Hi

Here is my simplified problem (see my other post after this one for one a
bit more complicated)

DoCmd.TransferSpreadsheet acImport, , "dbo.mytable", .......

in an Access 2000 project it works fine
I open this project with Access 2003 (with or without converting) and I have
an error 3078 saying the table dbo_mytable (notice the dot '.' transformed
in a '_' ) does not exists or the name is wrong...

What can I do?

Best regards
Christophe Niel

 

[Tor]

Hi Christophe

I am having the same problem. Searching the web, I find lots of others who
do too. But nobody seems to have a solution, and Microsoft seem blissfully
unaware.

If you happen to find a solution, pleeeeease post it here.

Tor


Christophe Niel skrev:

 

[gazzippy]

I import spreadsheets by no longer using the TransferSpreadsheet comman
but using ADO instead.

I am now using ADO to create a connection to the Excel spreadsheet
then looping through each row adding it to the table in Access (usin
an ADP so it is an SQL Server table)

The reason Access 2003 does not import them is that we currentl
specify the destination table for the spreadsheet as dbo.MyTable (wher
dbo is the table owner) but in Access 2003 you do not specify the db
bit (even though when using the wizard to import you have to select th
table with the dbo bit, which then fails, nice and consistent that!!).

If I remove the dbo bit Access 2003 then decides to import the table
but it checks to see if the table exists using the user name for th
connection to the SQL Server box (in this instance the user name i
MyUser) and the table name (MyTable) so the table it looks for i
MyUser.MyTable (not dbo.MyTable), which doesn't exist so Access 200
creates it.

So all together now....... Thanks Microsoft for a new and improve
inconsistent product.

Here's the code i use

Dim rsDest As New ADODB.Recordset
Dim rsSource As New ADODB.Recordset
Dim oConn As New ADODB.Connection
Dim strSQL As String

strFName = "C:\Temp.xls"
strRange = "Sheet1$A1:AM42"

rsDest.Open "dbo.MyTable", CurrentProject.Connection
adOpenKeyset, adLockOptimistic
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Dat
Source=" & strFName & ";" & "Extended Properties=Excel 8.0;"
strSQL = "SELECT * FROM [" & strRange & "]"

rsSource.Open strSQL, oConn, adOpenKeyset
adLockOptimistic

'// Loop through spreadsheet adding record to dbo.MyTable
Do While Not rsSource.EOF

rsDest.AddNew

rsDest![Surname] = rsSource![Surname]
rsDest![Forename] = rsSource![Forename]

etc...



rsDest.Update
rsSource.MoveNext

Loop

'// Close Recordsets
Set rsDest = Nothing
Set rsSource = Nothing
Set oConn = Nothin