-
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"
-
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).
-
I need the data & the definition. Assume I do not have the table already in SQL.
-
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
-
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
-
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
