Retrieving data from Access using VBA

Demorganafari

New Member
Joined
Jun 19, 2016
Messages
2
I am trying to use the code below to retrieve some data from Microsoft Access. However, the part of the code in italics below gets highlighted(when I debug) and I get the feedback :No value given for one or more required parameters. Can anyone help?
Sub GetUnsentTransfers()
' Page 481
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim WSOrig As Worksheet
Dim WSTemp As Worksheet
Dim sSQL As String
Dim FinalRow As Long
Set WSOrig = ActiveSheet
'FstNm As Variant, LstNm As Variant, HseNm As Variant)
'Build a SQL String to get all fields for unsent transfers
sSQL = "SELECT FstNm, LstNm, HseNm FROM Customer20"
sSQL = sSQL & " WHERE [FstNm] = " & Range("I2").Value & " And [LstNm]= " & Range("J2").Value

' Path to Transfers.mdb
MyConn = ThisWorkbook.Path & Application.PathSeparator & "Mydata1.mdb"

Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open MyConn
End With
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
rst.Open Source:=sSQL, ActiveConnection:=cnn, _
CursorType:=adOpenDynamic, LockType:=adLockOptimistic, _
Options:=adCmdText



Activeworksheet.Select
Range("N1:P1").EntireColumn.Clear

' Add Headings
Range("N1:P1").Value = Array("FstNm", "LstNm", "HseNm")

' Copy from the recordset to row 2
Range("N2").CopyFromRecordset rst

' Close the connection
rst.Close
cnn.Close

' Format the report
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row

' If there were no records, then stop
If FinalRow = 1 Then
Application.DisplayAlerts = False
WSTemp.Delete
Application.DisplayAlerts = True
WSOrig.Activate
MsgBox "There are no transfers to confirm"
Exit Sub
End If

End Sub
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

asecgeek

New Member
Joined
Apr 1, 2016
Messages
11
It looks to me like you're pulling firstname and lastname from your Excel worksheet and inserting it into the SQL statement, but you're not enclosing these string values in quotes. I think the Where part of the statement should look like this:

WHERE [FstNm] = '" & Range("I2").Value & "' And [LstNm]= '" & Range("J2").Value & "'"
 

Watch MrExcel Video

Forum statistics

Threads
1,114,571
Messages
5,548,843
Members
410,877
Latest member
RaeB
Top