Create a connection in excel for an ADO recordset

[Sorwen]

This is the first time I've tried to create an ADO recordset in excel. I've done so in access with success. I tried to do the same steps in Excel, but it errors out with variable not defined at the following code

Set con = CurrentProject.Connection

It highlights CurrentProject. con is Dim as Connection.

Any idea what I'm doing wrong

 

[PHV]

CurrentProject is a property of the Access.Application object ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fredericofonseca]

small example.

Sub putdata()
Dim wWork_sheet As Worksheet
Dim adoConn As ADODB.Connection
Dim adoComm As ADODB.Command
Dim strCmd As String
Dim i, x, y As Long

Dim sConn As String
Dim sUser As String
Dim sPass As String
Dim sSQL As String
sUser = InputBox("User name", vbOKOnly)
sPass = InputBox("Password", vbOKOnly)
Set adoConn = New ADODB.Connection
adoConn.ConnectionTimeout = 360
adoConn.Open "Provider=sqloledb;" & _
           "Data Source=boss_systst;" & _
           "Initial Catalog=target_datamirror;" & _
           "Uid=" & sUser & ";" & _
           "Pwd=" & sPass & ""
           
Dim aa As ADODB.Field
Set adoComm = New ADODB.Command
adoComm.CommandType = adCmdText
sSQL = ""
sSQL = sSQL & " INSERT INTO [target_db].[dbo].[mytbl]"
sSQL = sSQL & " (fld1, FLD2 )"
sSQL = sSQL & " VALUES (?, ?)"
adoComm.Parameters.Append adoComm.CreateParameter("p1", adChar, adParamInput, 1)
adoComm.Parameters.Append adoComm.CreateParameter("p2", adChar, adParamInput, 15)

adoComm.CommandText = sSQL
adoComm.ActiveConnection = adoConn
adoConn.BeginTrans
y = 0
i = 2
Set wWork_sheet = Workbooks("Book3").Worksheets("Sheet1")
While Not wWork_sheet.Cells(i, 1).Value = ""
  With adoComm.Parameters
    .Item("p1").Value = wWork_sheet.Cells(i, 1).Text
    .Item("p2").Value = wWork_sheet.Cells(i, 2).Text
  End With
  adoComm.Execute , , adExecuteNoRecords
  y = y + 1
  i = i + 1
  If y = 10001 Then
    y = 1
    adoConn.CommitTrans
    adoConn.BeginTrans
  End If
Wend
adoConn.CommitTrans
End Sub

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Sorwen]

Thank you both.