Opening a table 1

[Stainnd]

I'm writing this program that works with databases. In the program, you work with two different types of tables, both of which are created in this one database. So anyway, if a user goes to create a new table in, lets say format 1, here is the code i use:

Dim db as Database
Dim td as TableDef, TempTd as TableDef
Dim fields(5) As Field
Dim dbrecordset As Recordset
Tablename = InputBox("Enter title of table:", "New Table&quot
Set db = DBEngine.Workspaces(0).Opendatabase("c:\program files\olds\data\OLDS Database.mdb&quot
Set td = db.CreateTableDef(TableName)
Set fields(0) = td.CreateField("Category", dbText)
Set fields(1) = td.CreateField("Year", dbText)
Set fields(2) = td.CreateField("Title", dbText)
Set fields(3) = td.CreateField("BinderNum", dbText)
Set fields(4) = td.CreateField("FormNum", dbText)
Set fields(5) = td.CreateField("Comments", dbText)
td.fields.Append fields(0)
td.fields.Append fields(1)
td.fields.Append fields(2)
td.fields.Append fields(3)
td.fields.Append fields(4)
td.fields.Append fields(5)
db.TableDefs.Append td
Set dbrecordset = db.OpenRecordset(Tablename, dbOpenTable)

So that is the code when the user creates a new table.
When the user saves the table, the program creates a textfile named after data1.recordsource, and contains a single line of text containing data1.recordsource:
Open "c:\Program Files\OLDS\data\savedata\" + Data1.RecordSource + ".txt" For Output As #1
Print #1, Data1.Recordsource
Close #1

That works too. However, when i try to load a table, there is a form with a button and a Filelistbox which lists all the files in the savadata directory. When the button is clicked:

Dim opentablename
Open "C:\program files\olds\data\savedata\" + File1.Filename For Input As #1
Input #1, opentablename
Close #1
Index.Data1.RecordSource = opentablename
Unload Me
Index.show


However, when I do that, and i try to navigate around the opened table in the Index form, it doesn't recognize that i told it to open the tablename. Can someone please either help me fix my code, or tell me a better way to save/open tables. Thanks

-Mike

 

[daimaou]

I would suggest you use ADO Connection to access your database.

Let me know if you need help on how to use it.

btw I'm not sure of this, but instead of using:

Index.Data1.RecordSource = opentablename

Couldn't you use

Index.Data1.RecordSource = "SELECT * FROM '" & opentablename & "'"

to try it out, and is your opentablename variable really containing your table name?

 

[Stainnd]

What's this select stuff? I'm not really sure about what that is. Also, i guess i could try it with ADO, except that i don't know how to create a table in ado, where as this book i've been using showed me how to create one in dao, so that is the only reason i'm not so sure about that.

 

[daimaou]

well, if you're learning now I suggest you start learning ADO instead of DAO.
Also you'll probably need some SQL basis (That where the SELECT comes from ).

You can get some code to learn the basis on:

http://www.planet-source-code.com/xq/ASP/txtCodeId.12050/lngWId.1/qx/vb/scripts/ShowCode.htm

ADO is way more powerful than DAO and it would be your right pick if you want to start learning database access.

btw have you tryed your RecordSource to Index.Data1.RecordSource = "SELECT * FROM '[" & opentablename & "]'" to see if that solves your problem?

 

[Stainnd]

Thanks for the advice, I'm gonna check out that tutorial right away. However, regardless of whether i use DAO, ADO, or whatever, I still need a good way for my program to save and open the tables, as in store the tablenames in a variable or text file somehow, and so far all my previous attempts have been unsuccessful. Do you think you could help meout w/ that aspect too?
Thanks
-Mike

 

[Stainnd]

I just tried out the tutorial you gave me, and it looks like it'll work fine. Thanks for the help, and i'll post to confirm that it worked. Thanks again
-Mike

 

[Stainnd]

alright, i tried the tutorial theory in my program and it worked pretty well, except for this one part. For the RecourdSet Source, the tutorial has a set source, so it uses "Select * From Employee" or whatever, but i want a variable, instead of a constant like employee. I don't know SQL, so could u help me out w/ this? Thanks
-Mike

 

[daimaou]

hi again.

so instead of selecting records from the table Employee you want to select them from another table is that it?

This is preety easy to do, you only need to set the recordSource property by code.

I don't have that example here so I'm not sure wich is the control that you are talking about (Lets call it control1).

And let's say you have the name of your table on a variable MyTableName.

Somewhere on your code just do:

Control1.Recordsource = "SELECT * FROM '" & MyTableName & "'"

and that should do it. After this you'll probably need to make a control1.Refresh.

This should be it, let me know if you're having problems with anything else.

 

[Stainnd]

I just tried that, but i think you have the quotation marks in the wrong place or something, because it gave me an error. Also, since its ADO, it is rsRecordSet.Source = ...
where rsRecordSet is set = new ADODB.recordset

 

[daimaou]

oops you're right I found the error....

Just do:

rsRecordset.source = "SELECT * FROM " & MyTableName & ""

This should work now