This is for a problem/defect tracking system for products.
Per the thread title I am using ASP and MS Access.

I'm trying to keep the whole system very dynamic and simple for the user.
To achieve this. many data fields use drop down lists populated from a table.
Once finished, there will be an ability to edit the data in those tables.

One of the Drop Down lists is to the Model name of the device.
I want to be able to use the data from the Model name field to dynamically choose which table the data is added to.
There are many reasons why I'm using a seperate table for each product.

Process

1 - Data Entry Form
User enters the data into text fields and chooses other variables from drop lists presented in an ASP page.
When SUBMIT is clicked the data is posted to the next page.

2 - Data Process Page (invisible to the user)
On this page I have the following line of code to open the table

strSQL = "SELECT * FROM TABLE;"

I'd like to be able to use a variable for the table name.
The variable will come from the previous page in the form of a POST.
(it will be chosen from a drop list).

I imagine it should look similar to this:
strSQL = "SELECT * FROM &TABLENAME&;"

Any help greatly appreciated.
Rob

Sounds like an icky design, but you're the boss!

GeorgeV - You the man

Thanks so much.
I've been stuck on this for a bit.

Cheers
Rob

rjaric

what I do is

strSQL = "SELECT * FROM [TABLENAME];"
^ just make it easyer to read

strSQL = replace(strSQL,"[TABLENAME]",Request.Form("TableName"))

then it can come

strSQL = "SELECT * FROM [TABLENAME] WHERE personid=[ID];"

strSQL = replace(strSQL,"[TABLENAME]",Request.Form("TableName"))
strSQL = replace(strSQL,"[ID]",Request.Form("ID"))

...
...

Thanks myle.

I think your suggestion will give more flexibility to create dynamic Queries.

Much appreciated.
Rob

Hi Myle

I can get this to work:
strSQL = "SELECT * FROM [TABLENAME];"
strSQL = replace(strSQL,"[TABLENAME]",Request.Form("TableName"))

But this doesn't work:
strSQL = "SELECT * FROM [TABLENAME] WHERE personid=[ID];"
strSQL = replace(strSQL,"[TABLENAME]",Request.Form("TableName"))
strSQL = replace(strSQL,"[ID]",Request.Form("ID"))

I get the Error:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.
/Display.asp, line 31
Line 31 is:
rsBBT.Open strSQL, adoCon

I'm just using ASP, not ASP.NET.
In what environment did this work for you?

Thanks in advance
Rob

Why don't you Response.Write the strSQL variable to screen and see if you find a syntax error. If you struggle after this, post the results of the write here and we will take a look.

Sorry wrong hat on


msaccess needs the tablename after the select
strSQL = "SELECT [TABLENAME].* FROM [TABLENAME]
strSQL = replace(strSQL,"[TABLENAME]",Request.Form("TableName"))

I have this Sub in my main inc file
then when I want to see something on screen all i need to do is

call writeit("strSQL",strSQL)

it out is

strSQL=[SELECT [TABLENAME].* FROM [TABLENAME]]

Thanks GeorgeV and Myle,

I did a response.write to the screen and was able to see the SQL query.
Found that I needed to fine tune the first line a bit

So this: strSQL = "SELECT * FROM [TABLENAME] WHERE personid=[ID];"
Became:strSQL = "SELECT * FROM [TABLENAME] WHERE personid ='[ID]'"

Replace lines remained the same:
strSQL = replace(strSQL,"[TABLENAME]",Request.Form("TableName"))
strSQL = replace(strSQL,"[VALUE]", Request.Form("ID"))

So it was just two missing single quotes.
I did find that having ; at the end did nothing.

Thanks again.
Rob

You only need to quote character fields - I would have guessed that ID was a number...

And the semi-colon is good practice to include; it terminates the statement explicitly.