A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Table name as parameter in SQL query



 
 
Thread Tools Display Modes
  #1  
Old June 23rd, 2004, 01:48 PM
Art
external usenet poster
 
Posts: n/a
Default Table name as parameter in SQL query

Hi,

I'd like to create a query in Access that will prompt me for the table name from which to get the data. Since I'm not very familiar with SQL I normally create a query using the QBE grid, switch to SQL view and make changes.

Ultimately I'm going to use this query from a VB.net program and will be looping through a sequence of tables as my data sources.

I'd appreciate any help, hints or suggestions.

Thanks,

Art
  #2  
Old June 23rd, 2004, 05:10 PM
John Vinson
external usenet poster
 
Posts: n/a
Default Table name as parameter in SQL query

On Wed, 23 Jun 2004 05:48:03 -0700, Art
wrote:

Hi,

I'd like to create a query in Access that will prompt me for the table name from which to get the data. Since I'm not very familiar with SQL I normally create a query using the QBE grid, switch to SQL view and make changes.

Ultimately I'm going to use this query from a VB.net program and will be looping through a sequence of tables as my data sources.

I'd appreciate any help, hints or suggestions.


You cannot pass a table name as a Parameter to a query; you'll have to
have your VB.Net program actually construct a SQL string.

I'm queasy about your table design - multiple identically-structured
tables are generally A Bad Idea. Are you storing data in tablenames?
What are these tables?

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public
  #3  
Old June 23rd, 2004, 05:39 PM
Art
external usenet poster
 
Posts: n/a
Default Table name as parameter in SQL query

John,

Thanks for the response. What I'm doing is fairly simplistic and perhaps there's a better way. We get monthly text files. I need to analyze some data in the colllection of them, each month. I have an mdb that imports each text file into a unique table. I then have an append query which takes that table and adds it to my "master table".

I'm trying to automate some of this in VB.net and I'd want to call the append query, but pass a parameter to it, telling it which of the monthly imported tables to use. I don't want the import routine itself to append, because on occassion there are errors and I need to redo everything. In that case I can wipe out the master table and re-import all of the monthly ones -- they're really not very big -- each on the order of 10,000 records.

Anyway, that's what I'm trying to do. Thanks for your comments -- if you have any suggestions I'd be very anxious to hear them.

Art

"John Vinson" wrote:

On Wed, 23 Jun 2004 05:48:03 -0700, Art
wrote:

Hi,

I'd like to create a query in Access that will prompt me for the table name from which to get the data. Since I'm not very familiar with SQL I normally create a query using the QBE grid, switch to SQL view and make changes.

Ultimately I'm going to use this query from a VB.net program and will be looping through a sequence of tables as my data sources.

I'd appreciate any help, hints or suggestions.


You cannot pass a table name as a Parameter to a query; you'll have to
have your VB.Net program actually construct a SQL string.

I'm queasy about your table design - multiple identically-structured
tables are generally A Bad Idea. Are you storing data in tablenames?
What are these tables?

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

  #4  
Old June 24th, 2004, 02:14 AM
Dale Fye
external usenet poster
 
Posts: n/a
Default Table name as parameter in SQL query

Seems to me that you could import to a single table, then update the master
table from there. If there are errors you can always delete a single months
transactions from the master table. No need to store each month in a
separate table and in the master as well. It sounds like you are keeping
the monthly text files anyway, so you could always go back and redo the
entire process.

Dale


  #5  
Old June 24th, 2004, 01:17 PM
Art
external usenet poster
 
Posts: n/a
Default Table name as parameter in SQL query

Dale,

Thanks. I'm trying to do much of my work through VB.net -- something I'm just learning. I'll pop over to that discussion area to find out how to submit a command such as application.docmd.trasfertext - so that I may be able to do what you suggest.

Art

"Dale Fye" wrote:

Seems to me that you could import to a single table, then update the master
table from there. If there are errors you can always delete a single months
transactions from the master table. No need to store each month in a
separate table and in the master as well. It sounds like you are keeping
the monthly text files anyway, so you could always go back and redo the
entire process.

Dale



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:19 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.