Accessing database tables in VB2010 2

[Marcel49]

Hi,
I am rewriting a VB6 application in VB2010. The program needs to work with a third party database (Access 97). It seems that certain table names are 'reserved' and cannot be accessed by VB2010, though it works perfectly in VB6. I reduced the problem to the following code. The database 'TestDb.mdb' used has 5 identical, empty tables.

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
      Dim dbTest As New OleDb.OleDbConnection
      Dim TableName(5) As String
      TableName(0) = "Table"
      TableName(1) = "Index"
      TableName(2) = "Schema"
      TableName(3) = "Schem"
      TableName(4) = "Tabel"
      TableName(5) = "NonExisting"
      Dim Message As String = ""
      dbTest.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = TestDb.mdb"
      dbTest.Open()
      For N = 0 To 5
         Dim da = New OleDb.OleDbDataAdapter("SELECT * FROM " & TableName(N), dbTest)
         Dim ds As New DataSet
         Try
            da.Fill(ds, TableName(N))
            Message = Message & "The table " & TableName(N) & " is accepted!" & vbCr
         Catch ex As Exception
            Message = Message & "The table " & TableName(N) & " is not accepted: " & ex.Message & vbCr
         End Try
      Next
      MsgBox(Message)
      End
   End Sub

The following combined message is produced:

The table Table is not accepted: De component FROM bevat een syntaxisfout.
The table Index is not accepted: De component FROM bevat een syntaxisfout.
The table Schema is not accepted: De component FROM bevat een syntaxisfout.
The table Schem is accepted!
The table Tabel is accepted!
The table NonExisting is not accepted: De Microsoft Jet-database-engine kan de invoertabel of -query NonExisting niet vinden. Zorg ervoor dat deze bestaat en dat de naam correct is gespeld.

Why can't all these tables be accessed????

 

[jebenson]

Try putting square brackets around the table names.

TableName(0) = "

"
TableName(1) = "[Index]"
TableName(2) = "[Schema]"
TableName(3) = "[Schem]"
TableName(4) = "[Tabel]"
TableName(5) = "[NonExisting]"

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day !

 

[ZmrAbdulla]

jebenson is correct..
but a small correction.. for easy

Dim da = New OleDb.OleDbDataAdapter("SELECT * FROM[COLOR=red] ["[/color] & TableName(N) &[COLOR=red] "]"[/color], dbTest)

BTW, Why do you need reserved word as table name? You can use naming standards like "tblTable" always. Then take out "tbl" any time through code if you really need it.


Zameer Abdulla

 

[Marcel49]

Thanks to both Jebenson and Zameer Abdulla. It works fine. I'm just curious how it works and why it is necessary. Is there any general rule for using square brackets within strings?

Thanks again for the fast responses!

 

[ZmrAbdulla]

Normally when there is space in the name of the table or the field or the name is a reserved word

http://support.microsoft.com/kb/286335

Zameer Abdulla