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


"TL" <(E-Mail Removed)> wrote in message
news:7E390686-DE77-44CE-A028-(E-Mail Removed)...
> 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
>

I tried the default schema but that did not work. I will try the temp table
now.

"Alex Dybenko" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:7E390686-DE77-44CE-A028-(E-Mail Removed)...
> > 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
> >
> .
>