articles:

differences between jet and odbc

Consider these two connection strings:

"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & server.MapPath("\somedb.mdb")

"Driver={Microsoft Access Driver (*.mdb)};DBQ="& server.MapPath("\somedb.mdb")

I have never really noticed any major difference between these two connection strings in terms of what they do, and I have always been under the impression that one was just a wrapper around the other.

However, recently I stumbled upon two things the Jet.OLEDB data provider can do that the ODBC driver cannot:

  1. Use of SELECT @@IDENTITY to return the ID of the last record to be inserted within a current connection
  2. Something like this:

    dim conn
    set conn = server.createobject("ADODB.connection")

    dim rs
    set rs = server.createoobject("ADODB.recordset")

    conn.open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & server.MapPath("\somedb.mdb")

    rs.open "SELECT [tableA.somecolumn], [tableB.somecolumn] FROM [tableA], [tableB] WHERE [tableA.id = tableB.tableAid]",conn, 3

    do while not rs.EOF
        response.write(rs("tableA.somecolumn") & " " & rs("tableB.somecolumn"))
        rs.movenext
    loop

    rs.close
    set rs = nothing
    conn.close
    set conn = nothing

    If you use the ODBC driver with the above SQL you'll get an error saying that tableA.somecolumn is not in the collection.

Not necessarily the most ground breaking of scientific or articles, but may solve somebody's puzzle.

Comments

Posted on Tuesday, September 20, 2016 11:36:00 PM GMT by anonymous
good. thx 4 the info!

Post a comment

name:
(optional)

email:
(optional)

comment: