DoCmd.TransferDatabase acExport to SQL server 2005

  • Thread starter Thread starter TL
  • Start date Start date
T

TL

All,
I am trying to export a table from an Access 2007 database to a SQL server
2005 database. However I can not get the table to export to the correct
schema. it always goes to the dbo schema. Below is the code I am running.
When I run the table shows up in SQL as dbo.[schema.SQL_Tableusername] and I
need it to be schema.SQL_Tableuser

Any help would be helpful

Private Sub Command7_Click()
Dim db As Database
Dim username As String
Dim tablename As String

Set db = CurrentDb

username = Environ("USERNAME")
username = Replace(username, ".", "")
tablename = "" & "SQL_Table" & username & ""




On Error GoTo ErrorHandler2
DoCmd.TransferDatabase acExport, "ODBC Database", _
"ODBC;DSN=PAWCHDPARTDEV01;UID=MyUserID;PWD=MyPassword;LANGUAGE=us_english;" _
& "DATABASE=DPART_P008", acTable, "tbl_accounts", "schema." & tablename





'db.Execute (execLe10)

MsgBox "Done", vbInformation
Exit Sub
ErrorHandler1:
MsgBox "Failed1", vbInformation
ErrorHandler2:
MsgBox "Failed2", vbInformation

End Sub
 
Hi,
I guess that TransferDatabase exports to default user's schema, perhaps you
can try to change it before export and then put it back
Another approach - export to temp table and then move to required table
using Insert Into

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com
 
I tried the default schema but that did not work. I will try the temp table
now.

Alex Dybenko said:
Hi,
I guess that TransferDatabase exports to default user's schema, perhaps you
can try to change it before export and then put it back
Another approach - export to temp table and then move to required table
using Insert Into

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com


TL said:
All,
I am trying to export a table from an Access 2007 database to a SQL server
2005 database. However I can not get the table to export to the correct
schema. it always goes to the dbo schema. Below is the code I am running.
When I run the table shows up in SQL as dbo.[schema.SQL_Tableusername] and
I
need it to be schema.SQL_Tableuser

Any help would be helpful

Private Sub Command7_Click()
Dim db As Database
Dim username As String
Dim tablename As String

Set db = CurrentDb

username = Environ("USERNAME")
username = Replace(username, ".", "")
tablename = "" & "SQL_Table" & username & ""




On Error GoTo ErrorHandler2
DoCmd.TransferDatabase acExport, "ODBC Database", _
"ODBC;DSN=PAWCHDPARTDEV01;UID=MyUserID;PWD=MyPassword;LANGUAGE=us_english;"
_
& "DATABASE=DPART_P008", acTable, "tbl_accounts", "schema." & tablename





'db.Execute (execLe10)

MsgBox "Done", vbInformation
Exit Sub
ErrorHandler1:
MsgBox "Failed1", vbInformation
ErrorHandler2:
MsgBox "Failed2", vbInformation

End Sub
Click to expand...
.
Click to expand...
 
Back
Top