none
problems with sql syntax after migrating access database 97 to 2000 RRS feed

  • Question

  •  

    Hello,

    in VB.net I use the following code with an access 97 database Pazpatrim1.mdb:

    Dim cnLocal As New OleDb.OleDbConnection

    Dim cmmdLocal As New OleDb.OleDbCommand

    Dim drLocal As OleDb.OleDbDataReader

    cnLocal.ConnectionString = "provider = Microsoft.jet.oledb.4.0; Data Source =" & My.Settings.Pad & "\" & Patrimoniumfile

    cnLocal.Open()

    Dim sqlLocal As String = "SELECT * from [C:\PAZUZU VBNET\pazuzu\Pazpatrim1.mdb].t_patrimonium as a,[C:\PAZUZU VBNET\pazuzu\Pazuzu.mdb].t_aandelen as b, a inner join b on a.isincode=b.isincode"

    cmmdLocal = New OleDb.OleDbCommand(sqlLocal, cnLocal)

    drLocal = cmmdLocal.ExecuteReader

    drLocal.Read()

    Debug.Print(String.Format("ISINCODE = {0} ", (drLocal(0))))

    This works well.

    After converting the same database to access 2000 or 2003 it give error "invalid database format". How to solve this problem?

     

    Greetings,

    Bart

     

    Saturday, December 8, 2007 10:13 AM

Answers

  • You might want to try some slightly different connection syntax:

     

    Code Block

    Dim sqlLocal As String = "SELECT * from [MS Access;DATABASE=C:\PAZUZU VBNET\pazuzu\Pazpatrim1.mdb].t_patrimonium as a, [MS Access;DATABASE=C:\PAZUZU VBNET\pazuzu\Pazuzu.mdb].t_aandelen as b, a inner join b on a.isincode=b.isincode"

     

     

    Tuesday, December 18, 2007 3:15 PM
  • Great, this works fine,

    Thanks

    Saturday, December 22, 2007 8:10 AM

All replies

  • Hi flandersit,

     

    Error message ""invalid database format"

    1. Data Source =" & My.Settings.Pad & "\" & Patrimoniumfile

       -> Please ensure Data Source is referenced to available database file.

     

    2. Dim sqlLocal As String = "SELECT * from [C:\PAZUZU VBNET\pazuzu\Pazpatrim1.mdb].t_patrimonium as a,[C:\PAZUZU VBNET\pazuzu\Pazuzu.mdb].t_aandelen as b, a inner join b on a.isincode=b.isincode"

    -> I think you cannot operate two data sources (two database files Pazpatrim1.mdb and Pazuzu.mdb) in Select statement. You should operate two tables in the same data source (e.g. Pazpatrim1.mdb).

     

    Please check this code.

    Presume that the Pazpatrim1.mdb contains two table: t_patrimonium and t_aandelen.

    Code Block

            Dim cnLocal As New OleDb.OleDbConnection

            Dim cmmdLocal As New OleDb.OleDbCommand

            Dim drLocal As OleDb.OleDbDataReader

            cnLocal.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\Pazpatrim1.mdb"

            ' provider = Microsoft.jet.oledb.4.0; Data Source =" & My.Settings.Pad & "\" & Patrimoniumfile

            cnLocal.Open()

            Dim sqlLocal As String = "SELECT * from t_patrimonium as a, t_aandelen as b, a inner join b on a.isincode=b.isincode"

            cmmdLocal = New OleDb.OleDbCommand(sqlLocal, cnLocal)

            drLocal = cmmdLocal.ExecuteReader

            drLocal.Read()

            Debug.Print(String.Format("ISINCODE = {0} ", (drLocal(0))))

     

     

    Regards,

    Martin

    Wednesday, December 12, 2007 8:50 AM
  • Hello,

    thanks for the answer. But the problem is that I need to join two tables from different databases. And why does everything work for an Access 97 database, and when i convert them to access 2000 it doesn't work anymore.

     

    regards,

    Bart

     

    Monday, December 17, 2007 10:22 AM
  •  ntsmith wrote:

    ...

     

    i do this sort of thing loads and i thought you need a semi colon after [ so it is like

     

    Dim sqlLocal As String = "SELECT * from [;C:\PAZUZU VBNET\pazuzu\Pazpatrim1.mdb].t_patrimonium as a,[;C:\PAZUZU VBNET\pazuzu\Pazuzu.mdb].t_aandelen as b, a inner join b on a.isincode=b.isincode"

     

    Originally posted by user ntsmith.  Original post got moved with thread split (due to hijacking).  Original comment can be found in the thread http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2565939&SiteID=1

    Monday, December 17, 2007 10:48 PM
  • Move it from VB Language forum to .NET Framework Data Access and Storage forum for better responses.
    Tuesday, December 18, 2007 7:20 AM
  • You might want to try some slightly different connection syntax:

     

    Code Block

    Dim sqlLocal As String = "SELECT * from [MS Access;DATABASE=C:\PAZUZU VBNET\pazuzu\Pazpatrim1.mdb].t_patrimonium as a, [MS Access;DATABASE=C:\PAZUZU VBNET\pazuzu\Pazuzu.mdb].t_aandelen as b, a inner join b on a.isincode=b.isincode"

     

     

    Tuesday, December 18, 2007 3:15 PM
  • Great, this works fine,

    Thanks

    Saturday, December 22, 2007 8:10 AM