Member Avatar for wind_hiro

Hi, I have an access databse, but the data is link to excel, so i cant changes the data inside access database. However, i just want to retrieve the data inside the access database using VB datagridview, but the system pop out an error said 'cannot find the input table or query'
Is it because the data is link with excel, so access databse cannot direct take the data?
Below is my code:\

Dim myconnectionstring As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb"
myconnection = New OleDbConnection(myconnectionstring)
myconnection.Open()

query = ("SELECT Device,Package FROM [Sheet1$]")
mycommand = New OleDbCommand(query, myconnection)
dr = mycommand.ExecuteReader
While dr.Read

DataGridView1.Rows.Add()
DataGridView1.Rows(n).Cells("Column1").Value = dr("Device")
DataGridView1.Rows(n).Cells("Column2").Value = dr("Package")


End While
dr.Close()
myconnection.Close()

  • 2 Contributors
  • 2 Replies
  • 197 Views
  • 13 Hours Discussion Span
  • Latest Post Latest Post by G_Waddell

Dani AI

Generated

The error "cannot find the input table or query" usually means the SQL name used doesn't exist inside the Access file you opened. If the Access .mdb contains linked Excel objects, the name you must query is the linked-table name as Access exposes it — not necessarily the worksheet-style name you would use when connecting directly to an Excel file. 's suggestion to connect straight to Excel is a valid alternative, but if you want to go through the Access file you first need to discover the exact table names Access has for those links.

Quick way to discover table names programmatically (VB.NET):

Dim conn As New OleDbConnection(connStr)  ' connStr = your Access connection string
conn.Open()
Dim tables As DataTable = conn.GetSchema("Tables")
For Each r As DataRow In tables.Rows
    Debug.WriteLine(r("TABLE_NAME").ToString())
Next
conn.Close()

Once you have the correct Access table name, use a data adapter and bind the result to the DataGridView rather than adding rows manually:

Using conn As New OleDbConnection(connStr)
    Dim sql As String = "SELECT * FROM [" & tableName & "]"
    Dim da As New OleDbDataAdapter(sql, conn)
    Dim dt As New DataTable()
    da.Fill(dt)
    DataGridView1.DataSource = dt
End Using

Troubleshooting notes: open the .mdb in Access and check the linked-table names and the Linked Table Manager (relink if the source Excel moved). Confirm you’re using the right OLEDB provider for the Excel format (ACE vs Jet) and that your app’s bitness matches the installed provider. Wrap any table name that contains spaces or special characters in brackets. If the program still can’t find the table, try connecting directly to the Excel file as suggested — it often short-circuits naming/link issues.

Recommended Answers

All 2 Replies

Member Avatar for wind_hiro

Who can help me??? please... urgent!!!

Member Avatar for G_Waddell

Hi,

Why use the Access DB? you can link directly to the Excel document as if it was a database table.

Try looking at the Data Files section of http://www.connectionstrings.com for the excel version you are using.

Also you can google ADO.net and Excel or VB.Net and Excel and get loads of examples.

Edited by G_Waddell because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.