How do I change the command text to access a different table

[John_Lamb]

I have a system with question banks stored as tables in a A2K database. A user selects a table and this should then be able to change the command text before the main test form is loaded. I already have established a connection called connection1 and a command called tests. The associated fields do not need to change only the name of the name of the table in the command text.

I have made several attemps using ADO to do this without success.

help appreciated

John Lamb

[Iouri]

After you established connection, assign name of yor table to the variable (sYourTable) and open a recordset.

Set rs = New ADODB.Recordset
sYourTable = "MyTable1"

sSQL = "select * from " & sYourTable
rs.Open sSQL,Connection, adOpenDynamic,adLockOptimistick

When you want to open different table, just assign its name to the variable

Iouri Boutchkine
iouri@hotsheet.com

[John_Lamb]

Its still not working I'm afraid. I'll just outline a few things about how I set it up.

I created a connection to my Access 2K database
I created a command called tests.
I associated it with a table in the database.
I dragged accross the fields onto my form.

This all works fine if I only use questions from that table. I need to change the table name in the command text I think after I open the connection. This is the code I have in the form that selects the test.

'*****************************
Dim myTest As String
Dim ObjRec As ADODB.Recordset
Dim objConn As Connection
Set objConn = DataEnvironment1.Connection1
objConn.Open
myTest = Me.lstTests.Text ' Text string from list box containing the table name
Set ObjRec = New ADODB.Recordset
Dim sSQL As String
sSQL = "select * from " & myTest
ObjRec.Open sSQL, objConn, adOpenDynamic, adLockOptimistic

frmTest Show

'****************************************

[Iouri]

I think this code must work. Don't forget to close recordset before you assign myTest to different table

Iouri Boutchkine
iouri@hotsheet.com

[John_Lamb]

Success!!!!!!!
When you get that moment you remember why you program and put yourself through the torment.
I am very gratefull for your assistance Iouri.
My next challenge is to open up the management system accross the network and add test results at the end. It would be easy if I knew the path but that will be located on installation of the front end with a common dialog box. I will then have to store the path in a table and build up a connection string when needed. Wish me luck

Thanks again

John Lamb

[Iouri]

Good luck, John

Iouri Boutchkine
iouri@hotsheet.com