Updating Access From Excel VBA

[talhamoin]

I have the below code that is giving me the following error:


Sub myMacro()

Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset

con.ConnectionString = "DBQ=D:\My Data\Database\Database.mdb;" & _
"DRIVER={Microsoft Access Driver (*.mdb)};"

con.Open
Set rs.ActiveConnection = con

rs.Open "UPDATE purchase_invoices SET name='Mushtaq' where bill_no=1"

Do Until rs.EOF
Cells(1, 2) = rs.Fields(1).Value
rs.MoveNext
Loop

End Sub



Error:
Run-time error '-2147217904 (80040e10)':

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.


Can someone help with this?

Thanks

[Kenneth Hobs]

You can look at connectionstrings.com for driver syntax.

e.g.
'http://www.microsoft.com/downloads/details.aspx?FamilyID=6c050fe3-c795-4b7d-b037-185d0506396c&DisplayLang=en

Option Explicit
'Connection Strings, http://www.connectionstrings.com/

'http://msdn.microsoft.com/en-us/library/ms808325.aspx

Sub ADO()
' Set Reference in Tools to: Microsoft ActiveX Data Objects 2.x Library
Dim DBFullName As String
Dim Cnct As String, Src As String
Dim Connection As adodb.Connection
Dim RecordSet As adodb.RecordSet
Dim Col As Integer, Row As Long, s As String

' Database information
DBFullName = ActiveWorkbook.Path & "\NWind2003.mdb"
'Exit?
If Dir(DBFullName) = "" Then Exit Sub

'Clear any existing data from activesheet
Cells.Clear

' Open the connection
Set Connection = New adodb.Connection
Cnct = "Provider=Microsoft.Jet.OLEDB.4.0; "
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct

' Create RecordSet
Set RecordSet = New adodb.RecordSet
' Record locking
RecordSet.CursorType = adOpenKeyset
RecordSet.LockType = adLockOptimistic

With RecordSet
' Filter
'Src = "SELECT * FROM Products WHERE ProductName = 'Watch' "
'Src = Src & "and CategoryID = 30"
Src = "SELECT Orders.CustomerID, Orders.OrderDate " & _
"FROM Orders " & _
"WHERE (((Orders.OrderDate) " & _
"Between #8/1/1994# and #8/30/1994#))"
RecordSet.Open Source:=Src, ActiveConnection:=Connection

' Write the field names
For Col = 0 To .Fields.Count - 1
Range("A1").Offset(0, Col).Value = RecordSet.Fields(Col).Name
Next Col

' Write the recordset
Range("A1").Offset(1, 0).CopyFromRecordset RecordSet
Dim a As Variant
.MoveFirst
a = RecordSet.GetRows
MsgBox LBound(a), , UBound(a)
MsgBox a(0), , a(1)

If .RecordCount < 1 Then GoTo endnow
.MoveFirst
For Row = 0 To (.RecordCount - 1)
'Debug.Print CStr(.Fields(Row).Value)
.MoveNext
Next Row
End With
endnow:
Set RecordSet = Nothing
Connection.Close
Set Connection = Nothing
End Sub