ADP - Copy mdb table to SQL Server

[Terry]

The database is an Access 2003 ADP. I want to copy data from an Access
database (mdb) table to SQL Server. I can do this using DTS but would prefer
to use an ADO transfer so that I can do extensive data manipulation on
selected fields (plus I am not real flash on DTS). I can get the ADO to read
the Access database but cannot get the SEEK working on the table in SQL
Server. I tried setting the commandtype to TableDirect but this caused an
error. I would appreciate the lines of code to create the recordset ready for
using the SEEK.
I suspect that the ConnectionString Provider I am using is incorrect - using
OleDb.
Code used is:
Set cnnDest = New ADODB.Connection
cnnDest.ConnectionString = "Provider='sqloledb';Data
Source='<servername>';Initial Catalog='<tblname>';Integrated
Security='SSPI';""
cnnDest.Open
Set cmdDest = New ADODB.Command
Set cmdDest.ActiveConnection = cnnDest
cmdDest.CommandType = adCmdTableDirect
cmdDest.CommandText = "tblRequests"
Set RsDest = cmdDest.Execute
Thanks
Terry

 

[Microsoft newsgroup]

Whell Terry

Try to Link the table on sql to your MDB by:
1. choosing Link tables
2. in the bottom of the screen there is combobox.
choose ODBC which is the bottom of the combobox.
3. in there choose use connection string and the wizart will assist you to
connect to sql server and to your database.
4. After you connected a list of all the tables in sql will appear. - choose
the table you want to use (be aware that if the table on sql server does not
have primary key you can't edit it on access. also if there is datatype that
access can't recognize like bigint, calculated field ect... there will be a
problem using this table)
5. now new table is being added to your mdb with picture of globus and black
arrow this is your link table. it probebly be add with dbo_<table name>

now you can use ADO of local access to do what ever you want with the table.

Good luck.

 

[Sylvain Lafontaine]

Seek is not available with SQL-Server. For adCmdTableDirect, I'm not sure
but I won't be surprised if it's not available either.

 

[Terry]

Thanks for your response.
This is an Access Data Project in Access 2003 & the tables are displayed in
the project as it is connected to the database on SQL Server from the start;
I am not sure whether this means they are 'linked'. However, I believe,
please correct me if I am wrong, using ODBC severely limits your options
including response times.
Notwithstanding any of the above, opening a table using ADO in code requires
a ConnectionString, etc. What should I be using?
Terry

 

[Terry]

Thanks for your response Sylvain
Yes! In the Help files SEEK appears to be only available via TableDirect but
SQL Server is not mentioned. I guess this is part of what I am trying to
clarify. The Help files are not particularly useful.
Terry

 

[Sylvain Lafontaine]

No need to check any further in the HELP documentation as I know for sure
that Seek is not supported for any major databases (SQL-Server, Oracle,
etc.) other than JET/Access.