MS Access to SQL Server with specific schema

I'm trying to copy an MS Access table ("tbl_Review_Step2") into a SQL Server table ("ReviewTable_Step2").

I wrote the following code, which works, but it puts the table into the dbo schema.  I'm trying to figure out what to change in order to create the table in a different schema?

SELECT * INTO [ODBC;DRIVER=SQL Server;SERVER=myServerName;DATABASE=myDatabaseName;Trusted_Connection=Yes].[ReviewTable_Step2] FROM tbl_Review_Step2



Thank you!
LVL 2
DonCompensation Manager, Reporting & AnalysisAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
Change the Server and Database name.
BitsqueezerCommented:
Hi,

I never tried that but it may work if you add the schema name between the ODBC connection string part and the table name:
SELECT * INTO [ODBC;DRIVER=SQL Server;SERVER=myServerName;DATABASE=myDatabaseName;Trusted_Connection=Yes].SchemaName.[ReviewTable_Step2] FROM tbl_Review_Step2

Open in new window


The very much easier way is to do that using the Access upsizing tool or even better the Microsoft SQL Server Migration Assistant which you can find here:

Microsoft SQL Server Migration Assistant v6.0 for Access

Cheers,

Christian
DonCompensation Manager, Reporting & AnalysisAuthor Commented:
Thanks, All.

In reference to the first comment . . . I did not want to supply the "real" server and database names so I put myServerName and myDatabaseName here but in the project VBA, I put in the real names and it does work.  But it creates the table in the default schema instead of the schema I want it put in.

In reference to the second comment . . . I did try putting the schema name in between the ODBC string and the table name and it didn't like that either.  I'll do some more research on Microsoft SQL Server Migration Assistant.  I'm not actually trying to migrate a whole database to SQL Server and I have the code to create a table.  I just need it to go into the correct schema from the start.

Until I come up with something better, my workaround solution is to let the code create the table in the dbo schema, use T SQL to copy that table to the secondary schema, and then use T SQL to drop the table from the dbo schema.

Thanks!
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Vitor MontalvãoMSSQL Senior EngineerCommented:
You can do it directly in SQL Server by using the Impor Data Wizard available in SQL Server Management Studio (SSMS). Just right clcik on the desired target database, chose All Tasks / Import Data... and follow the wizard.
Other option is to create a Linked Server in SQL Server to the MS Access database and run the query from SQL Server side.
BitsqueezerCommented:
Hi,

normally you should have no other reason to create a table on the server than migrating a local table to SQL Server. If you try that method for any kind of "normal" database application that's the wrong way to go.
Except temp tables there should never be any need to create a real table in the normal table area of your SQL Server database at runtime. That would be extremly riskful if more than one user uses this database at the same time. Not to mention that you kill any performance advantage SQL Server gives you through indexes, execution plans, saved views, cached queries and so on.

So it would be good if you could describe what you want to achieve with this construct, I guess we could find a better way to do that.

By the way, instead of using SELECT INTO on the local database you could do it vice-versa, using T-SQL to open the Access database with OPENROWSET and transfer the data into the desired target table, then you should have no problem with the schema.

Another way could be to create a SQL Server login which has the desired schema as default schema and use that to create the target table so that any new object is in the desired schema.

A third way could be to use ADO in VBA to do the OPENROWSET variant described above, the advantage here is that you can use local databases at every file location of the Access database because you can setup the SQL string containing OPENROWSET with a dynamic built string, OPENROWSET cannot use variables, always needs a constant string.

But again: Maybe there is a much better way instead of trying to create a table from local data, so please describe what you want to do with this method.

Cheers,

Christian
DonCompensation Manager, Reporting & AnalysisAuthor Commented:
I agree 100%.  A better solution is definitely available.  I know it and will research more when I have time.  Unfortunately, I don't have a lot of time right now and needed to come up with a quick solution, which I did.

Thank you all for your input.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
The Import Data Wizard is quite fast to implement. After all is a wizard :)
DonCompensation Manager, Reporting & AnalysisAuthor Commented:
This question was time-sensitive, requiring a quick solution and quick implementation.  The quick solution sought was not found but the final solution implemented was quick.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.