Microsoft Jet 4.0

[Kurt]

Hello Everyone,

I have the following code:

Sub command_parameters()
    Dim conn As New Connection
    Dim rec As New Recordset
    Dim comm As Command
    Dim ws As Worksheet
    Dim countryname$
    Set ws = ThisWorkbook.Worksheets("command")
    conn.Open "Provider=microsoft.jet.oledb.4.0;" + _
        "Data Source=" + ThisWorkbook.Path + "C:\NWind.mbd;"
    Set comm.ActiveConnection = conn
    comm.CommandText = _
        "SELECT companyname FROM Customers WHERE Country = ?"
    countryname = InputBox("Please input the name of a country " & _
        "(for example, 'germany').")
    rec.Open comm
    ws.[a1].CopyFromRecordset rec
    rec.Close: conn.Close
End Sub

I have VBA For Applications, Microsoft Excel 11.0 Object Library, OLE Automation, Microsoft Active X Data Objects 2.5 Library check in Tools, References. The Active X Data Objects 2.5 Library should reference Jet 4.0. My code keeps stoping on this line:

conn.Open "Provider=microsoft.jet.oledb.4.0;" + _
        "Data Source=" + ThisWorkbook.Path + "C:\NWind.mbd;"

What am I forgetting here? I had it working in another spreadsheet with the same references. The worksheet is not protected.

TIA

Kurt

 

[Richard Schollar]

Try replacing the + with the concatenation operator &.

Richard

 

[pcc]

ThisWorkbook.Path + "C:\NWind.mbd"

will return sometrhing thast can't be resolved

do you mean
ThisWorkbook.Path & "\NWind.mbd"?

 

[Kurt]

concantenation

Hello,

I tried replacing the + with a & and still no luck.

    conn.Open "Provider=microsoft.jet.oledb.4.0;" + _
        "Data Source=" + ThisWorkbook.Path + "\nwind.mbd;"

I now get a error message run time error: some number.

Any other thoughts. Thanks for your efforts.

Kurt

 

[Kurt]

concantenation

Hello All,

Fixed it!

I got past the jet statement then bombed on the rec.Open comm line and realized I left the line out above it.

Regis this is the final answer!

Sub command_parameters()
    Dim conn As New Connection
    Dim rec As New Recordset
    Dim comm As New Command
    Dim ws As Worksheet
    Dim countryname$
    Set ws = ThisWorkbook.Worksheets("command")
    conn.Open "Provider=microsoft.jet.oledb.4.0;" + _
        "Data Source=" + ThisWorkbook.Path + "\nwind.mdb;"
    Set comm.ActiveConnection = conn
    comm.CommandText = _
        "SELECT companyname FROM Customers WHERE Country = ?"
    countryname = InputBox("Please input the name of a country " & _
        "(for example, 'germany').")
    comm.Parameters(0) = countryname
    rec.Open comm
    ws.[a1].CopyFromRecordset rec
    rec.Close: conn.Close
End Sub

Thanks again for those who replied!

Have a great and product day!

Kurt