VBA Excel Module opening Access DB with Recordset

[jonsan]

Dim cConn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sSql As String

sSql = "SELECT Table1.Field1,Table1.Field2, Table1.Field3 FROM Table1 WHERE Table1.Field4 = Yes"
    
Set cConn = New ADODB.Connection
Set rs = New ADODB.Recordset

cConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='Location of Access Database'"

Set rs = New ADODB.Recordset

rs.Open sSql, cConn

I get the same error on the rs.Open statement every time: "Run-time Error '-2147217904 (80040e10)' No value given for one or more required parameters"

Any thoughts?

Thanks,
J

 

[Chance1234]

Source='Location of Access Database'"

what is your source path ?

Chance,

Filmmaker, taken gentleman and He tan e epi tas

 

[DaveInIowa]

I don't know if this is causing the problem but shouldn't you enclose 'Yes' in single quotes? Also, you're instantiating a New ADODB.Recordset a second time needlessly.

 

[jonsan]

Here is the connection string:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='\\path\files\folder\database.mdb'"

I use this path succesfully to execute SQL statements with a ADODB Connection object in a different Macro.

Thanks,
Jon

 

[RoyVidar]

I would start with checking carefully the spelling of the fields, in particular the on in the where clause, but also the rest of the field names.

Roy-Vidar

 

[SkipVought]

BTW,

You ALSO need the PATH in the FROM clause.

Typically, I store the PATH and DB (sans file extension) in 2 variables and substitute in BOTH the connect string AND the FROM clause.

    sConn = sConn & "DBQ=" & sPath & "\" & sDB & ".xls;"
    sConn = sConn & "DefaultDir=" & sPath & ";"


    sSQL = sSQL & "FROM `" & sPath & "\" & sDB & "`.`Query$` "

Skip,

_{[red]Be Advised![/red] A Last Will and Testament is...
A Dead Giveaway!}