Re-Querrying using a different Table: query, sql, table

by: richtsteigPosted on 1998-12-02 at 04:25:40ID: 1969850

I don't know exactly what your main problem is.

Regarding your question title, I think the problem is that you cannot use a parameter for a tablename like

SELECT * FROM [AnyTable]

What you can do, is to store your "sourcequery" using a mark like [AnyTable] and run your query after replacing the marks for your module.

Example:
Dim thisSQL as string
thissql = MyDB.QueryDefs("SourceQuery").sql 'getting the sample query
thissql = strReplace(thissql,"[AnyTable]",DestinationTable) 'changing tablenames
MyDB.Execute SQL

Please help with some more detailed information if I misunderstood your question.

by: HoganPosted on 1998-12-08 at 17:46:14ID: 1969851

richtsteig,

Using example names for all the objects in play, please help me with the reference (dimming) to the textboxes where I will place the name of the table source and the table target (for this one depending on which query I am modifying it will either create a new table or will append to one - for this case I will have to copy -format only - of the one I need to use and rename it accordingly based on what I will type in the textboxes.

At the end and ideally, I will end up with a code that will:

1.- Modify the source and target table of a the make a table Query1.

2.- Modify the source and target table of a the make a table Query2.

3.- Copy a table format only under a different name.

4.-Modify the source and target table of an apppend to a table Query3.

The textboxes in the form will hold the sources and target tables names and the name for the table used to do the append to table query.

If the concept is more clear now and wish to help me with it, please quote me the additional points you require to complete the project.

Let me know if you need further clarification of what in earth am I trying to do.

Thanks again . . .

H o g a n . -

by: HoganPosted on 1998-12-31 at 21:42:15ID: 1969852

richtsteig,

I am sorry but I need help with detailed information to make it work.

I hope you undertand. I am not a programmer yet.

Thanks for trying to help me.

Hogan.-

by: brewdogPosted on 1999-02-16 at 13:14:06ID: 1969853

How about these steps, Hogan. (Sorry if this is late -- the question got buried back here a ways.)

It sounds like what you're trying to do is, after importing data from a text file into a table:

1. Do a make-table query with the original data.
2. Take the new table you just made and run another make-table query on it.
3. Copy the table structure, but not the data, of an existing table and then append records from this new import.

Build your form with text boxes (or combo boxes) for the name of the source table and the destination table. On the button click, do:

Dim strSQL as string
strSQL = "select * from " & txtSourceTable & " into " & txtDestinationTable & ";"
docmd.setwarnings false
docmd.runSQL strSQL
docmd.setwarnings true

This would be the syntax for the first make-table query. I don't know if your query needs to summarize the data or just copy it all in. If you need to only take certain fields, then instead of the asterisk above, you'd need to type out the names of the fields.

The second make-table would be the same idea. I'm guessing that you have some criteria to specify or some summarizing to do, or else you wouldn't be running two or more make-table queries. If so, let me know what all needs to be summarized/criteria'd and I'll work on that part.

For the third part, you could do something like this:

DoCmd.CopyObject acTable, [NameOfYourSourceTable], acStructureOnly

You'll definitely want to check on the third piece there -- I'm not sure what Access' syntax is for Structure only/no data. Then you could do the Dim SQL string again for the append. The only difference is, instead of Select Into, it is Insert Into. And if the field names aren't the same from your new "blank" table and the table from which you are appending records, you'll need to type out which fields go where, in the appropriate order.

Hope this helps some . . .

brewdog

by: MoondancerPosted on 2002-03-17 at 19:33:18ID: 6875867

GREETINGS!

This question was awarded, but never cleared due to the JSP-500 errors of that time. It was "stuck" against userID -1 versus the intended expert whom you awarded. This corrects the problem and the expert will now receive these points; points verified.

Please click on your Member Profile and select "View Question History" to navigate through any open or locked questions you may have to update and finalize them. If you are an EE Pro user, you can also choose Power Search to find all your open questions.

This is the Community Support link, if help is needed, along with the link to All Topics which reflects many TAs recently added. Also in the Community Support link is a question on how experts can help, if they wish, on the cleaning of old and abandoned questions.

http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
http://www.experts-exchange.com/jsp/zonesAll.jsp

Thank you,
Moondancer
Moderator @ Experts Exchange

Question

Re-Querrying using a different Table

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

query

sql

table

Microsoft Access Database

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Need individual assistance?

Our experts are ready to help.

Want to learn from the best?

Read articles from industry experts.

Working on a long term project?

Store your work and research.

What Makes Experts Exchange Unique?

Trusted by the world's most respected brands.

Faithfully serving IT professionals since 1996.

Related Solutions

Free Tech Articles

Cloud Class Webinars

Join the Community

Give a Little. Get a Lot.

Answers

3 Ways to Join

The Experts

The Experts

Testimonials

Testimonials

Testimonials

Business Clients

Business Clients

In the Press

In the Press

In the Press