I am using the VB code below in MS Access to insert a record in a SQL table.
The code works fine.

I now want to move this code to an ActiveX script in DTS (SQL 2000) but get
errors when I Parse the code. Can anyone please expain what I'm doing wrong?

many thanks

Stephen Lonsdale


'recordset and connection variables
Dim Cnxn As ADODB.Connection
Dim rstTest As ADODB.Recordset
Dim strCnxn As String
Dim strSQL As String

' Open a connection
Set Cnxn = New ADODB.Connection
strCnxn = "Provider=SQLOLEDB.1;Data Source=(local); Initial
Catalog=test_db;user id = 'sa';password='test_pwd'"

Cnxn.Open strCnxn

Set rstTest = New ADODB.Recordset
strSQL = "sys__event_tracker"
rstTest.Open strSQL, strCnxn, adOpenKeyset, adLockOptimistic, adCmdTable


rstTest.AddNew
rstTest!event_category = "test message"
rstTest.Update

Hello Stephen,

The conversion would not be that hard to do but the question I would have
is why?

In DTS you have a Connection type for Access and a Connection type for SQL
Server. These should serve you perfectly well unless you have some overwhelming
requirements of the insert.


Allan