VB Datagrid to pull access file.

[lspeed430]

I'm trying to make a Flexgrid program that will enable me to press a command button to go to the next table in an Access file.
Every time the com2 button is pressed - then Cate = Cate+ 1 (Cate being the table number(1-13 in this case) viewed by the user
Tabl(1-Cate) is the name of that Table.
For example: Tabl(1)="Bills"


Here's the program so far:

Option Explicit
' Note: 'Dim' will work the same as 'Private' here ...
Private Cate As Integer
Private Tabl() As String


Private Sub Form_Load()

Dim db As Database
Dim td As TableDef
Dim intX As Integer

Set db = OpenDatabase("C:\My Documents\phone numbers.mdb")
For Each td In db.TableDefs
' There are hidden, system tables in an Access DB
' that start with "MSys", which you want to ignore
If Left$(td.Name, 4) <> "MSys" Then
intX = intX + 1
ReDim Preserve Tabl(1 To intX)
Tabl(intX) = td.Name
End If
Next
db.Close

Cate = 1
dbPhone.RecordSource = "SELECT * FROM " & Tabl(1)

End Sub

Private Sub com2_Click()
Cate = Cate + 1
If Cate > UBound(Tabl) Then intCate = 1
dbPhone.RecordSource = "SELECT * FROM " & Tabl(Cate)
dbPhone.Refresh
End Sub

**** Now my question is:
When I run this program - I get an error message:
**
The Microsoft Jet database engine could not find the object "SELECT * FROM Bills." Make sure the object exists and that you spell its name and the path name correctly.

Now when I pull up the Access file (phone1.mdb) the first table is Bills.
The first column is Bills/Phone Number/Address/Account Number/Notes
What I think the reason for the error is the fact that it is searching for "MSys"
Does anybody else have any other ideas:
For a more fuller explanations look up web page:
http://forums.vb-world.net/showthrea...9328#post89328

[Paul Warren]

lspeed430 - this might be a bit too obvious but doesn't the fact that you close the database before executing the search mean that Jet doesn't know where to look for the tables ? Does making the Database object a module level var solve the problem ?

I don't know if you've included all the code but if you have then that's your problem.

[lspeed430]

What I did was put a ' in front of the db.Close
and it still gives me the same error message.
If I'm missing more code, please fill me in because I'm clueless as to the proper method on this.
I'm just learning.

Kind regards,
Stephen

P.S. Anyway I can get in touch with Bruce Gordon???
He was the one that recommended going with this code.



[Edited by lspeed430 on 07-31-2000 at 12:51 PM]