none
My DSN-less connection string to Access works in ADO but not ADO.Net RRS feed

  • Question

  • I just can't seem to get this working.

     

    Code Snippet

    Dim ConnectSource As New Odbc.OdbcConnection

    Dim Adapter As Odbc.OdbcDataAdapter = New Odbc.OdbcDataAdapter

    Dim tabDest As New Data.DataTable("MlocAK")

     

    ConnectSource.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\EnvironProcessing\Q3_2008\FinalMDBs\EnvironMaster_Loc_AK.mdb;User Id=admin;Password=;"

     

    SQL = "Select * From MlocAK"

    Adapter.SelectCommand = New Odbc.OdbcCommand(SQL, ConnectSource)

    Adapter.Fill(tabSource)

     

     

    I get the error "ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified" on the Fill.

     

    I have verified six ways from sundaty that the path to the MDB is correct.

     

    This code in VB6 works just fine:

     

    Code Snippet

    Dim connX As New ADODB.Connection

    Dim rs As New ADODB.Recordset

     

    ConnX.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\EnvironProcessing\Q3_2008\FinalMDBs\EnvironMaster_Loc_AK.mdb;User Id=admin;Password=;"

     

    rs.Open "Select * From MLocAK", connX

     

     

    I was having ADO.NET nightmares last night over this and (as I discovered this morning) datatable.ImportRow not changing the @#^$%* RowState!

     

    What am I doing wrong?

     

    Thanks,

    Brad

    Wednesday, August 13, 2008 5:23 PM

Answers

  • You are trying to use Jet OLEDB provider with ODBC .NEt provider. You need to use OleDb namespae from .NET and your code should look like

     

    Dim ConnectSource As New Odbc.OdbcConnection

    Dim Adapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter

    Dim tabDest As New Data.DataTable("MlocAK")

     

    ConnectSource.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\EnvironProcessing\Q3_2008\FinalMDBs\EnvironMaster_Loc_AK.mdb;User Id=admin;Password=;"

     

    SQL = "Select * From MlocAK"

    Adapter.SelectCommand = New OleDb.OleDbCommand(SQL, ConnectSource)

    Adapter.Fill(tabSource)

    Wednesday, August 13, 2008 9:18 PM
    Moderator

All replies

  • You are trying to use Jet OLEDB provider with ODBC .NEt provider. You need to use OleDb namespae from .NET and your code should look like

     

    Dim ConnectSource As New Odbc.OdbcConnection

    Dim Adapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter

    Dim tabDest As New Data.DataTable("MlocAK")

     

    ConnectSource.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\EnvironProcessing\Q3_2008\FinalMDBs\EnvironMaster_Loc_AK.mdb;User Id=admin;Password=;"

     

    SQL = "Select * From MlocAK"

    Adapter.SelectCommand = New OleDb.OleDbCommand(SQL, ConnectSource)

    Adapter.Fill(tabSource)

    Wednesday, August 13, 2008 9:18 PM
    Moderator
  • Yup, that is absolutely what it was.

     

    Can't believe I lost sleep over a head slapper like this.

     

    Not all lost. I did learn how to programmatically add an Access DSN using VB.NET.

     

    Thanks!

     

    - Brad

    Wednesday, August 13, 2008 9:47 PM