Question about accessing various tables in Access with VB6

[GenocideOwl]

ok, I need to have three levels of data for a database. I need an overall inventory, I need where/how much of each stock is, and of each part of that stock I need info about how much came from the original order. I think I explained that right. anyway. I need to know if during an ADO command if I can use variables. like with this command

Set rs = New ADODB.Recordset 
rs.Open "TBIC", cn, adOpenKeyset, adLockPessimistic, adCmdTable

could instead of just tpying in the text TBIC, if I could use a variable name. like read what the name of the table where the item information is and then tell it to open that table.

also I was wondering how you could create a new table with the right format in VB6?

[edit] I also was wondering about about filling in information, I will have various companies that own different products. If I need to get information based on company and then product, can I set "field1" to the company and "field2" to the product then just tell it to fill the rest of the fields in?

I know I am asking a lot...

[Al42]

Set rs = New ADODB.Recordset 
rs.Open "TBIC", cn, adOpenKeyset, adLockPessimistic, adCmdTable

could instead of just tpying in the text TBIC, if I could use a variable name.

rs.Open strYourStringVariable, cn, adOpenKeyset, adLockPessimistic, adCmdTable

The variable would be the Select statement:

strYourStringVariable = "Select * From " & txtTableName.Text & " Where ..."

also I was wondering how you could create a new table with the right format in VB6?

Read this thread.

I also was wondering about about filling in information, I will have various companies that own different products. If I need to get information based on company and then product, can I set "field1" to the company and "field2" to the product then just tell it to fill the rest of the fields in?

First, you want a manufacturer table with company name, address, contact person, etc., and an ID number. In the product table you have a "manufacturer" field, that holds the ID number from the manufacturer table. There are a couple of reasons to do it this way (called "normalization"):

1) If you have to change something about a manufacturer (Esso changes its name to Exxon), there's just one place to change it.

2) It takes a lot less room to store a number (4 bytes) than all the information about the manufacturer.

As far as automatically filling things in, which things? If you mean manufacturer data, it's already "filled in" as soon as you enter the ID number - it's in the manufacturer table.

When you show it on screen or print it, you just link the query to the manufacturer table and you get the product information and the manufacturer information in one recordset.