I have a simple macro that I want to use to pull information from an Access database. Each time I run it, I get the "No value given for one or more required parameters" error. I am wondering if because the Access table is read only that it is somehow affecting the Select statement? I have checked the spelling and columns, and they are correct.

Sub GetSalary()
Dim rsData As New ADODB.Recordset
Dim sPath As String
Dim sConnect As String
Dim sSQL As String
Dim PEN_FOFR As String

PEN_FOFR = "V101"

Sheet1.UsedRange.Clear

sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = C:\Documents and Settings\bmahony\desktop\FOFR Data.mdb;"

sSQL = "Select Salary_Monthly_FT From StaffData_Abra Where PEN=" & PEN_FOFR & ";"

Set rsData = New ADODB.Recordset

rsData.Open sSQL, sConnect, adOpenForwardOnly, adLockReadOnly
If Not rsData.EOF Then
Sheet1.Range("A1").CopyFromRecordset rsData
Else
Sheet1.Range("A1").Value = "No staff found"
End If

rsData.Close
Set rsData = Nothing

End Sub

What is StaffData_Abra, a table or query?

It is a linked table from SQL to an Access db

Try this for your sql string instead.

sSQL = "Select Salary_Monthly_FT From StaffData_Abra Where PEN=" & Chr(34) & PEN_FOFR & Chr(34) & ";"

It appears that PEN is a text field so the parameter value must have quotes around it.

Apostrophes might also work:

sSQL = "Select Salary_Monthly_FT From StaffData_Abra Where PEN='" & PEN_FOFR & "';"

In case it's not obvious, there are apostrophes surrounding the variable.

Martin