CODEWITHSUNDEEP
vb.netms-access
hunterex
hunterex

Reputation: 595

OleDbException was unhandled when OleDbDataAdapter function is used

I am using the first answer from Read Access File into a DataSet to prepare a dataset from Access file.

Here is the code snippet of the solution:

Using cn = New OleDbConnection(connectionstring)
cn.Open()
Dim ds As DataSet = new DataSet()

Dim Schema As DataTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
For i As Integer = 0 To Schema.Rows.Count - 1
    Dim dt As DataTable = New DataTable(Schema.Rows(i)!TABLE_NAME.ToString())

    Using adapter = New OleDbDataAdapter("SELECT * FROM " + Schema.Rows(i)!TABLE_NAME.ToString(), cn)
        adapter.Fill(dt)
    End Using

    ds.Tables.Add(dt)
Next i
End Using

I have "Syntax error in FROM clause" with OleDbException was unhandled when "i" counter is 62.

When I check the "ds.Tables.List" in QuickWatch, ds.Tables(61) has its table name as "xxxTable".

Meanwhile, in Microsoft Access, table 62 name is "xxxTable(abc)"

In other words, the string expression for "SELECT * FROM " + Schema.Rows(i)!TABLE_NAME.ToString() when i = 61; "SELECT * FROM xxxTable"

when i = 62; "SELECT * FROM xxxTable(Subsystem 0)"

How do I handle the table names with ( ) ?

Thank you

Upvotes: 1

Views: 98

Answers (1)

dotNET
dotNET

Reputation: 35460

Enclose your table name in square brackets, i.e. the particular line would become:

Using adapter = New OleDbDataAdapter("SELECT * FROM [" + Schema.Rows(i)!TABLE_NAME.ToString() + "]"c, cn)

Upvotes: 1

Related Questions