Make a table in SS from Access

Question

Make a table in SS from Access

Os-984017

SSC Enthusiast

Points: 184
More actions
September 16, 2010 at 1:50 pm

#226568

I have this code to make a table in SS from Access. It worked fine as far as creating a new generic table. But I needed to create a table with data records from other tables that are currently linked to Access from SS.
Here's the original code:
************************
Sub CreateServerTable()
Dim cmd As New ADODB.Command
cmd.ActiveConnection = "Provider=SQLOLEDB;Data Source =MyServer;Database=MydB;UID=me;password=whatever;"
cmd.ActiveConnection.CursorLocation = adUseClient
cmd.CommandType = adCmdText
cmd.CommandText = "Create Table tblMyTest(rowID int Identity(1,1),Origin varchar(50),Datefld datetime,Destination varchar(50))"
cmd.Execute
cmd.ActiveConnection.Close
End Sub
*****************
Then I replaced the create Table statement with this one:
cmd.CommandText = "Create Table tblMyTest AS(Select*From currentTable Where any criteria here);"
but it didn't work.
1. How can I run a make table qry from Access to make a new table in SS and the data will come from other tables already in SS and linked to Access.
2. How can I run an existing macro from Access to spit out a result in SS?
Basically, we have a big application that originally written in Access, and we are moving into SS. We exported the tables to SS and linked them back in Access. The queries/macros are still in Access and we need to execute them against the tables in SS.
I'd appreciate any help.
Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

randy-719186 Valued Member Points: 50 More actions · Answer 1

If I understand correctly you can just use SSIS wait the Access DB as the Source and SQL Server as the destination. It will import the data and create the tables at the same time.

Os-984017 SSC Enthusiast Points: 184 More actions · Answer 2

I'm sorry, but I'm not sure if I understood your response. Could you please clarify?

Remember, the tables are already in SS and linked to Access. All the queries/macros/codes are in Access.

Thanks

randy-719186 (9/17/2010)
If I understand correctly you can just use SSIS wait the Access DB as the Source and SQL Server as the destination. It will import the data and create the tables at the same time.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 3

You're running passthrough queries from Access, right?

If so, SQL Server is a two part statement. First 'create table blah AS ( bId int, value1 varchar(50))... etc.

THEN, you do an insert into blah select fields from previous table.

OR, you do SELECT fields INTO newtable FROM oldtable WHERE x.

I know it's psuedocode, but once you swap to using MSSQL backend, you need to learn to use passthrough queries for your DDL statements. You can still use localized queries for insert/update and the like, but you'll also see better performance if you start to convert those to passthrough as well and use Access just as the cheap front end interfacing.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Os-984017 SSC Enthusiast Points: 184 More actions · Answer 4

Thanks Craig. So, what you are saying is to make a new table with desired field names in SS first, then instert records into that table from other tables (also in SS). But you cannot make a new table with records from other tables in one statement. I was hoping there might be an easy way (or do both steps into a single step). I will give it a try on Monday and let you know.

You see, we are trying to avoid re-writing, or at least minimize the efforts for an application that contain 30 tables, 100 queries, and 20-30 macros that run various operations: delete, update, append, and create new tables. Since we are dealing with a lot of data, we decided to move the tables from Access into SS and continue to us Access as a front end and execute our processes from Access.

Thanks.

Craig Farrell (9/18/2010)
You're running passthrough queries from Access, right?
If so, SQL Server is a two part statement. First 'create table blah AS ( bId int, value1 varchar(50))... etc.
THEN, you do an insert into blah select fields from previous table.
OR, you do SELECT fields INTO newtable FROM oldtable WHERE x.
I know it's psuedocode, but once you swap to using MSSQL backend, you need to learn to use passthrough queries for your DDL statements. You can still use localized queries for insert/update and the like, but you'll also see better performance if you start to convert those to passthrough as well and use Access just as the cheap front end interfacing.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 5

Os-984017 (9/18/2010)
But you cannot make a new table with records from other tables in one statement. I was hoping there might be an easy way (or do both steps into a single step).

You can, but you want to do the second one I mentioned, SELECT cola, colb, ... INTO <newtable> FROM <oldTable>. Sorry I wasn't clear about the syntax. The SELECT INTO is used to create new tables from other data. The only problem with it is that you don't always have enough control on the table creation. You may want numerics and end up with floats, for example.

You will probably still want to do this as a passthrough and avoid the necessary network traffic and let it all happen locally on SQL Server.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA