By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,082 Members | 2,108 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,082 IT Pros & Developers. It's quick & easy.

Using Variables as Table Names

P: 11
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
Nov 20 '06 #1
Share this Question
Share on Google+
23 Replies


ronverdonk
Expert 2.5K+
P: 4,258
You are in the wrong (MySQL) forum. I'll redirect it.

Ronald :cool:
Nov 20 '06 #2

NeoPa
Expert Mod 15k+
P: 31,494
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.
Expand|Select|Wrap|Line Numbers
  1. 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.
Nov 20 '06 #3

P: 11
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
Nov 21 '06 #4

Expert 5K+
P: 8,434
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.
Nov 21 '06 #5

P: 11
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......
Nov 21 '06 #6

Expert 5K+
P: 8,434
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?
Nov 21 '06 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. 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] 
  3. FROM Document_corr2 
  4. WHERE Document_corr2.contract_no=[Froms]![YourFormName]![contract_No]
  5. ORDER BY Document_corr2.EE_Ref_no;
  6.  
Nov 21 '06 #8

P: 11
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.CreateTableDef(contract_no)
With Df
.Fields.Append .CreateField("Date_Issued", dbText)
.Fields.Append .CreateField("EE_Ref_no", dbText)
.Fields.Append .CreateField("Trim_Ref_no", dbText)
.Fields.Append .CreateField("Subject", dbText)
.Fields.Append .CreateField("Correspondence_from", dbText)
End With
End If

DB.TableDefs.Append Df

Set Dt = DB.OpenRecordset(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!Correspondence_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
Nov 21 '06 #9

P: 11
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. 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] 
  3. FROM Document_corr2 
  4. WHERE Document_corr2.contract_no=[Froms]![YourFormName]![contract_No]
  5. ORDER BY Document_corr2.EE_Ref_no;
  6.  
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.
Nov 21 '06 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
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.

Expand|Select|Wrap|Line Numbers
  1.  Dim qdf As QueryDef
  2. Dim strSQL As String 
  3.  
  4. Set db = CurrentDb
  5. Set qdf = db.QueryDefs("YourQueryName")
  6.  
  7. strSQL = "SELECT Document_corr2.EE_Ref_no AS [EE Ref#], " & _
  8.      "Document_corr2.Trim_Ref_no AS [Trim Ref#], Document_corr2.Subject, " & _
  9.      "Document_corr2.Correspondence_from AS [Correspondence] " & _
  10.      "FROM " & [contract_no] & _
  11.      " ORDER BY Document_corr2.EE_Ref_no;"
  12. qdf.Execute strSQL
  13. qdf.close
  14.  
Nov 21 '06 #11

P: 11
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.

Expand|Select|Wrap|Line Numbers
  1.  Dim qdf As QueryDef
  2. Dim strSQL As String 
  3.  
  4. Set db = CurrentDb
  5. Set qdf = db.QueryDefs("YourQueryName")
  6.  
  7. strSQL = "SELECT Document_corr2.EE_Ref_no AS [EE Ref#], " & _
  8.      "Document_corr2.Trim_Ref_no AS [Trim Ref#], Document_corr2.Subject, " & _
  9.      "Document_corr2.Correspondence_from AS [Correspondence] " & _
  10.      "FROM " & [contract_no] & _
  11.      " ORDER BY Document_corr2.EE_Ref_no;"
  12. qdf.Execute strSQL
  13. qdf.close
  14.  

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.
Nov 21 '06 #12

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Nov 21 '06 #13

P: 11
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("Doc_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
Nov 21 '06 #14

Expert 5K+
P: 8,434
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! :)
Nov 21 '06 #15

MMcCarthy
Expert Mod 10K+
P: 14,534
Try this:

Expand|Select|Wrap|Line Numbers
  1.  Function Correspondence_list() 
  2. Dim qdf As QueryDef
  3. Dim strSQL As String
  4. Dim DB As Object
  5. Set DB = CurrentDb
  6. Set qdf = DB.QueryDefs("Doc_Corres_Query")
  7.  
  8. strSQL = "SELECT EE_Ref_no AS [EE Ref#], Trim_Ref_no AS [Trim Ref#], Subject, Correspondence_from AS Correspondence," & _
  9. "FROM " & [contract_no] & ";"
  10. qdf.Execute strSQL
  11. qdf.Close
  12. End Function
  13.  

BTW, a data type converstion error is where you are trying to pass something that doesn't have the same value type as the object you are trying to pass it into.

What is the value in combobox [contact_no]. I assumed from your previous posts it was the table name. If not, what is it?

Mary
Nov 21 '06 #16

Expert 5K+
P: 8,434
..."data type conversion error"
Are you sure that's how the .Execute method is used? My guess (I'm no expert in this area, though) is that .Execute doesn't want a string. More likely you need to change the .SQL property, then use .Execute method to run it.

Don't take this as Gospel, though. I'm much more comfortable in VB than Access.
Nov 21 '06 #17

Expert 5K+
P: 8,434
...What is the value in combobox [contact_no]. I assumed from your previous posts it was the table name. If not, what is it?
I don't suppose the combobox could be tripping you up by having a different default property to what you expect? (Just asking, I have no idea.)
Nov 21 '06 #18

MMcCarthy
Expert Mod 10K+
P: 14,534
Are you sure that's how the .Execute method is used? My guess (I'm no expert in this area, though) is that .Execute doesn't want a string. More likely you need to change the .SQL property, then use .Execute method to run it.

Don't take this as Gospel, though. I'm much more comfortable in VB than Access.
I think you're right. It's been a while since I've used this. Damn, going on that memory again.

Try replacing:

qdf.Execute strSQL

with

qdf.SQL = strSQL

If that doesn't work let me know, I'll look it up.

Mary
Nov 21 '06 #19

MMcCarthy
Expert Mod 10K+
P: 14,534
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.

Hey, that's what I said! :)
I suspect that what will be required is a normalisation of the correspondence table. Probably broken out over different types of correspondence, each tied to the contracts table by contract number. Without seeing the full structure of the correspondence table I can't make any further guesses.

However, if Number5 would like some assistance on this I would be glad to provide it. Just send me a list of the fields and their datatypes of all fields in the contracts and correspondence tables. Along with an explanation of what the field refers to when its not obvious.

Mary
Nov 21 '06 #20

Expert 5K+
P: 8,434
I suspect that what will be required is a normalisation of the correspondence table. Probably broken out over different types of correspondence, each tied to the contracts table by contract number. Without seeing the full structure of the correspondence table I can't make any further guesses.
Sounds about right, though database design was never my strong-point.
Nov 21 '06 #21

NeoPa
Expert Mod 15k+
P: 31,494
Expand|Select|Wrap|Line Numbers
  1. qdf.Execute strSQL
You cant specify a SQL string with this format.
You need the Connection or Database (.Execute) method to pass a SQL string.

Execute Method


Runs an action query or executes an SQL statement on a specified Connection or Database object.

Syntax

object.Execute source, options

querydef.Execute options

The Execute method syntax has these parts.

Part Description
object A Connection or Database object variable on which the query will run.
querydef An object variable that represents the QueryDef object whose SQL property setting specifies the SQL statement to execute.
source A String that is an SQL statement or the Name property value of a QueryDef object.
options Optional. A constant or combination of constants that determines the data integrity characteristics of the query, as specified in Settings.
Nov 21 '06 #22

Expert 5K+
P: 8,434
querydef.Execute options
Hey, what do you know? I was right! :)
Nov 21 '06 #23

MMcCarthy
Expert Mod 10K+
P: 14,534
Hey, what do you know? I was right! :)
Had to happen sometime ;)
Nov 21 '06 #24

Post your reply

Sign in to post your reply or Sign up for a free account.