473,322 Members | 1,379 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Using Variables as Table Names

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
23 15437
ronverdonk
4,258 Expert 4TB
You are in the wrong (MySQL) forum. I'll redirect it.

Ronald :cool:
Nov 20 '06 #2
NeoPa
32,556 Expert Mod 16PB
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
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
Killer42
8,435 Expert 8TB
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
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
Killer42
8,435 Expert 8TB
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
14,534 Expert Mod 8TB
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
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
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
14,534 Expert Mod 8TB
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
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
14,534 Expert Mod 8TB
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
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
Killer42
8,435 Expert 8TB
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
14,534 Expert Mod 8TB
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
Killer42
8,435 Expert 8TB
..."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
Killer42
8,435 Expert 8TB
...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
14,534 Expert Mod 8TB
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
14,534 Expert Mod 8TB
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
Killer42
8,435 Expert 8TB
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
32,556 Expert Mod 16PB
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
Killer42
8,435 Expert 8TB
querydef.Execute options
Hey, what do you know? I was right! :)
Nov 21 '06 #23
MMcCarthy
14,534 Expert Mod 8TB
Hey, what do you know? I was right! :)
Had to happen sometime ;)
Nov 21 '06 #24

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

Similar topics

1
by: David | last post by:
Hi, I currently administer our MySQL db version 3.23.56 on a Linux Cobalt Qube server via an external fron-end piece of software. My Table Names have capital letters at the beginning, i.e....
2
by: ewu | last post by:
Hello all, I got a chance to peak into a database system. Part of its design is rather unfamiliar to me. When I look at the diagram generated by SQL Server, there are many floating tables. ...
8
by: rong.guo | last post by:
Greetings! I am now doing one type of analysis every month, and wanted to creat table names in a more efficient way. Here is what happens now, everytime I do the analysis, I will create a...
1
by: Jens Riedel | last post by:
Hello, we are planning to port an application from one database to different others, including DB2. While reading the GettingStarted document for DB2 Personal Edition I got a little confused...
6
by: Bernd Koehler | last post by:
Hi: I am a EE prof currently teaching an IT course. One the assignments students recently completed was designing a small MS Access Database. I have two submissions that are remarkably...
2
by: Brian O'Haire | last post by:
Hi I am trying to determine the column names in a table in an access xp database using c# in an asp.net web page. I can determine the table names by reading the MSysObjects table. The SQL system...
0
by: Robin Munn | last post by:
I'm developing a simple proof-of-concept Web application, more as a personal programming exercise than anything else, that presents the user with a login form where they can type in a database...
5
by: adi | last post by:
Hi all, Seems like a fundamental question to me but I dont have a definite answer for it, Gurus please enlighten me. I have a table 'Table1' whose structure changes dynamically based on some...
3
by: David C. Barber | last post by:
Using SQL Server 2000 and moving to a new computer. We did a full backup of the existing database to tape, brought up the new computer with a clean install using the same server name and IP...
1
by: Arne Gulbrandsen | last post by:
How to use Variables as Table Names in MS Access
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.