Select and Insert 130 records, MSAccess to MSSql 1

[Webkins]

I am trying to transfer data from an Access 2003 table into MSSql table using the this query:

SQLa = "Select F1, F13 from [abs fesa]"
Set rs = conn.Execute(SQLa, , adCmdText)
F1 = rs.Fields("F1"): F13 = rs.Fields("F13")

SQLc = "Insert into CoilSpec.dbo.schedule_final (F1, F13) values ('" & F1 & "','" & F13 & "');"
Set rcst = cn.Execute(SQLc, , adCmdText)

But I am only getting one record into SQL. Is it possible to select the entire amount of data and insert all 130 records at once without looping through all of them.
Thanks.

 

[Remou]

There are several ways. You can use ADO, you can use a linked table, which is probably the easiest, you can use a pass through query, or you can use DSN, for example:

Set db = CurrentDb

'DNS=Suitable system DNS for MSSQL
'Then, depending on your set up, you can incude:
'Database=DBName;
'Trusted_Connection=Yes;

strSQL = "INSERT INTO [ODBC;DSN=MSSQL_DB;].TableName " _
       & "SELECT ColName1, ColName2 FROM AccessTableName;"

db.Execute strSQL, dbFailOnError

http://lessthandot.com