Using Variables as Table Names

[Number 5]

Hi,

I was wondering if anyone has any idea how to use a variable as a table name in an SQL query in MS Access. My database creates a table based on a contract number (named after the contract number) and I wish to be able to retrieve info from a table based on the contract number on the form.

contract_no is a dropdown box on the form. It is the name of the table also.

I have gotten this code so far,

SELECT Document_corr2. EE_Ref_no AS [EE Ref#], Document_corr2. Trim_Ref_no AS [Trim Ref#], Document_corr2. Subject, Document_corr2. Correspondence_ from AS [Correspondence]
FROM [contract_no]
ORDER BY Document_corr2. EE_Ref_no;

I don't know how to use the FROM clause to take the dropdown box value as the table name.

thanks for your help

[ronverdonk]

You are in the wrong (MySQL) forum. I'll redirect it.

Ronald :cool:

[NeoPa]

Hi,

I was wondering if anyone has any idea how to use a variable as a table name in an SQL query in MS Access. My database creates a table based on a contract number (named after the contract number) and I wish to be able to retrieve info from a table based on the contract number on the form.

contract_no is a dropdown box on the form. It is the name of the table also.

I have gotten this code so far,

SELECT Document_corr2. EE_Ref_no AS [EE Ref#], Document_corr2. Trim_Ref_no AS [Trim Ref#], Document_corr2. Subject, Document_corr2. Correspondence_ from AS [Correspondence]
FROM [contract_no]
ORDER BY Document_corr2. EE_Ref_no;

I don't know how to use the FROM clause to take the dropdown box value as the table name.

thanks for your help

What you need to do is build up the SQL string in code.
Say you have a variable call cmbTabName.

strSQL = "SELECT * FROM [" & cmbTabName & "] WHERE ...."

You just add the static bits of the string to the variable and make up a string which is sensible SQL.

[Number 5]

Oh ok - I thought I may have to do that.

This may seem like a stupid question, but the Listbox used to display the results is based on a query - how do you get the listbox to display data from VBA code? Do I just run the coded SQL and assign the result to the listbox? Am I making sense?

Sorry - I am an engineer - not a programmer

Thanks in advance

[Killer42]

Oh ok - I thought I may have to do that.
This may seem like a stupid question, but the Listbox used to display the results is based on a query - how do you get the listbox to display data from VBA code? Do I just run the coded SQL and assign the result to the listbox? Am I making sense?

I think it's just matter of plugging in your shiny new SQL string as the row source for the listbox, then issuing a ReQuery on the listbox.

[Number 5]

Hmmm - I tried to do that and it comes up with a message saying that the record source specified on the form doesn't exist. The table it is trying to access does exist - so I'm not sure what the problem is there......

[Killer42]

Hmmm - I tried to do that and it comes up with a message saying that the record source specified on the form doesn't exist. The table it is trying to access does exist - so I'm not sure what the problem is there......

I might have my properties mixed up there - it might not be row source.

Can you show us some more detail of what you've got in your form? And listbox?

[MMcCarthy]

Doesn't work the way you're describing. contract_no has to be a field in the Document_corr2 table. Then you can use something like the following:

 
SELECT Document_corr2.EE_Ref_no AS [EE Ref#], Document_corr2.contract_no, Document_corr2.Trim_Ref_no AS [Trim Ref#], Document_corr2.Subject, Document_corr2.Correspondence_from AS [Correspondence] 
FROM Document_corr2 
WHERE Document_corr2.contract_no=[Froms]![YourFormName]![contract_No]
ORDER BY Document_corr2.EE_Ref_no;

[Number 5]

Yes - I just have to be careful not to breach my company's confidentiality code.

It is a form with several different tabs with different areas of information relating to a particular contract. The user selects the contract number from a combo box and all the information relating to that specific contract is brought up. The tab I am working with is a document correspondence tab. I have constructed code as such that rather than all the correspondence being in the one table (which would be eventually too large to handle), it creates a new table for each contract as the first correspondence record is entered. My code for that:

*** Code starts here**

Dim Df As TableDef
Dim DB As Database
Dim Dt As Recordset

If MsgBox("Save this new correspondence information?", vbExclamation Or vbYesNoCancel, _
"Confirm") <> vbYes Then
Me.Undo
Else
If counte = 0 Or counte = -1 Then
contract_no = [Forms]![Ergon Contracts]![contract_no]

Set DB = CurrentDb
Set Df = DB.CreateTableD ef(contract_no)
With Df
.Fields.Append .CreateField("D ate_Issued", dbText)
.Fields.Append .CreateField("E E_Ref_no", dbText)
.Fields.Append .CreateField("T rim_Ref_no", dbText)
.Fields.Append .CreateField("S ubject", dbText)
.Fields.Append .CreateField("C orrespondence_f rom", dbText)
End With
End If

DB.TableDefs.Ap pend Df

Set Dt = DB.OpenRecordse t(contract_no)

Dt.AddNew
Dt!Date_Issued = [Forms]![Document Correspondence_ form]![Date_Issued]
Dt!EE_Ref_no = [Forms]![Document Correspondence_ form]![EE_Ref_no]
Dt!Trim_Ref_no = [Forms]![Document Correspondence_ form]![Trim_Ref_no]
Dt!Subject = [Forms]![Document Correspondence_ form]![Subject]
Dt!Corresponden ce_from = [Forms]![Document Correspondence_ form]![Correspondence_ from]
Dt.Update
Dt.Close

End If
Exit Sub

**end of code**

So, If I then want to bring up information about document correspondence in relation to that contract, I have to be able to take the contract number, use that as the table name and run a query that returns the info I want from the table I want - in the listbox. The listbox is called "Corres". Does that make sense?

Thanks so much for your help

[Number 5]

Doesn't work the way you're describing. contract_no has to be a field in the Document_corr2 table. Then you can use something like the following:

 
SELECT Document_corr2.EE_Ref_no AS [EE Ref#], Document_corr2.contract_no, Document_corr2.Trim_Ref_no AS [Trim Ref#], Document_corr2.Subject, Document_corr2.Correspondence_from AS [Correspondence] 
FROM Document_corr2 
WHERE Document_corr2.contract_no=[Froms]![YourFormName]![contract_No]
ORDER BY Document_corr2.EE_Ref_no;

Oh yeah, thanks mmccarthy - just realised I forgot to remove the Document_corr2 from the beginning of each field in the query. I originally had the info coming from a single table called Document_corr2. I am still faced with the same problem though.

[MMcCarthy]

Overtime I could probably think of a neater way to do it.

Just one question you say the table would get too large. How Large?

If I stick with your code for the moment, I understand what you want from the original query is it just to choose a table name. You can't do that directly in the query. However, you can set up a dummy query and use the QueryDef feature.

 Dim qdf As QueryDef
Dim strSQL As String 
 
Set db = CurrentDb
Set qdf = db.QueryDefs("YourQueryName")
 
strSQL = "SELECT Document_corr2.EE_Ref_no AS [EE Ref#], " & _
	 "Document_corr2.Trim_Ref_no AS [Trim Ref#], Document_corr2.Subject, " & _
	 "Document_corr2.Correspondence_from AS [Correspondence] " & _
	 "FROM " & [contract_no] & _
	 " ORDER BY Document_corr2.EE_Ref_no;"
qdf.Execute strSQL
qdf.close

[Number 5]

Overtime I could probably think of a neater way to do it.

Just one question you say the table would get too large. How Large?

If I stick with your code for the moment, I understand what you want from the original query is it just to choose a table name. You can't do that directly in the query. However, you can set up a dummy query and use the QueryDef feature.

 Dim qdf As QueryDef
Dim strSQL As String 
 
Set db = CurrentDb
Set qdf = db.QueryDefs("YourQueryName")
 
strSQL = "SELECT Document_corr2.EE_Ref_no AS [EE Ref#], " & _
	 "Document_corr2.Trim_Ref_no AS [Trim Ref#], Document_corr2.Subject, " & _
	 "Document_corr2.Correspondence_from AS [Correspondence] " & _
	 "FROM " & [contract_no] & _
	 " ORDER BY Document_corr2.EE_Ref_no;"
qdf.Execute strSQL
qdf.close

Large as in over time - probably thousands of records per contract with about anywhere up to 100 contracts. I assumed that would be too large to store in the one table. I tried your dummy query - so do I then put the name of the dummy query in the record source section of the properties of the listbox? I tried that and it doesn't like it very much.

[MMcCarthy]

Large as in over time - probably thousands of records per contract with about anywhere up to 100 contracts. I assumed that would be too large to store in the one table.

It all depends on what your are trying to do with the queries. Some people have millions of records in a table. The size has more to do with a combination of record count and field count (plus other factors we won't mention. As long as your database doesn't go over 2GB (2000, higher I think in 2003) you should be alright.

I tried your dummy query - so do I then put the name of the dummy query in the record source section of the properties of the listbox? I tried that and it doesn't like it very much.

Look at the sql statement it assigns whatever value is currently in the combobox to the from as the table name.

Where does the listbox come in. If you want to use the resulting query as the rowsource for a listbox then just create a query of one sample table give it a name and use that for the row source.

The query then gets rewritten with the .execute

You would probably need to add a Me.Listboxname. requery at the end of your code.

[Number 5]

Do you know what a "data type conversion error" is?

I have used the code you suggested:

Function Correspondence_ list()
Dim qdf As QueryDef
Dim strSQL As String
Dim DB As Object
Set DB = CurrentDb
Set qdf = DB.QueryDefs("D oc_Corres_Query ")

strSQL = "SELECT EE_Ref_no AS [EE Ref#], Trim_Ref_no AS [Trim Ref#], Subject, Correspondence_ from AS Correspondence, " & _
"FROM [" & contract_no & "];"
qdf.Execute strSQL
qdf.Close
End Function


and it stops at this line:

qdf.Execute strSQL


Thanks - I hate to be a pain

[Killer42]

It all depends on what your are trying to do with the queries. Some people have millions of records in a table. The size has more to do with a combination of record count and field count (plus other factors we won't mention. As long as your database doesn't go over 2GB (2000, higher I think in 2003) you should be alright.

What's that? My ears are tingling...

No, it's definitely not higher in Access 2003. I wish... :(

But yes, it depends entirely on what's in the data, and the indexes. I have databases with over 15 million records. On the other hand, these are not very large records.

Hang on, let's do a quick calculation. If we assume 500,000 records then they could average no larger than around 4,294 bytes each - and that includes indexes, wasted space that hasn't been compacted yet, and so on. Judging by the term "correspondence " I'd say it's probably much safer to assume it will grow too large for a single table.

One table per contract does seem excessive, though. You might want to check the online help (or consult mmccarthy) for the maximum number of tables allowed.

...
You would probably need to add a Me.Listboxname. requery at the end of your code.

Hey, that's what I said! :)