Copy Access Table to SQL Server & vice-versa

[Elias]

Does anyone know how I can copy an Access table using ADO code. I looked in the help for the SELECT INTO statement and I cannot seem to get it to work. The following is an example of what I am trying to accomplish:
rs.open "select * " & _
"into cable IN SQLDATABASE " & _
"FROM MDBDATABASE.TABLE"

Serge Suggested the following; however, I am not sure of the syntax:

Dim cn As New ADODB.Connection

cn.Open "DSN=MyDSN;UID=MyID;PWD=MyPassword;"
cn.Execute "Insert Into SQLTable Select * From AccessTable


Of course, your table structure should be the same in both tables.

This may work, but I want the table to be created as in a select into...Elias

Also, I am not sure of the exact syntax for "AccessTable"

[Clunietp]

Do you need to copy the data AND the table definition at runtime, or just the data?

If you just need the data, read in a recordset, clear the SQL server table, then append all rows from your recordset into the destination table.

I believe you can only copy a table from one DB to another using Jet SQL, not ANSI (standard) SQL or T-SQL (SQL Server).

[Elias]

I need the data & the definition. Assume I do not have the table already in SQL.

[Clunietp]

Well that's not very fun! You can create the table in SQL Server, then generate an SQL script for table creation. Use that T-SQL code to create your SQL server tables at runtime.

You could also probably use the SQL Server DTS library for importing and exporting your databases via code, OR create a DTS package on your SQL server, and call that via DTS (this way is probably much easier)

This all assumes you are using SQL 7

That is about all I can suggest for now, it is too late for me to do this kind of db coding, plus I don't want to do ALL of your work for you! :)

Tom

[Elias]

What does the T-SQL code for table creation look like? I don't want to have to give it field sizes, datatypes, etc.

Please help!

-Elias

[Clunietp]

Merry Xmas! I pulled this out of SQL server using the Pubs database, authors table...

CREATE TABLE [dbo].[authors] (
[au_id] [id] NOT NULL ,
[au_lname] [varchar] (40) NOT NULL ,
[au_fname] [varchar] (20) NOT NULL ,
[phone] [char] (12) NOT NULL ,
[address] [varchar] (40) NULL ,
[city] [varchar] (20) NULL ,
[state] [char] (2) NULL ,
[zip] [char] (5) NULL ,
[contract] [bit] NOT NULL
)

HTH

Tom