A simple ADO dynamic connection problem: - specifying table at runtime 1

[thewhip]

The code below works to dreate a dynamic connection to a database which can be specified at runtime.<br>
However the line which is commented out which, that was to allow me to specify which table to connect to at runtime also, is not working. I get:<br>
Runtime error 3709 The application requested an operation on an object with a reference to a closed or invalid connecttion object.<br>
How do I specify table at runtime<br>
<br>
Dim Db As Connection<br>
Dim dbname As String 'database name<br>
Dim tname As String 'table name<br>
Dim sqlselect As String 'select statement<br>
Dim Rs As Recordset<br>
dbname = &quot;A&quot;<br>
tname = &quot;1&quot;<br>
Set Db = New Connection 'setting database connection<br>
Db.Open &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\F\Databases\Stocks\&quot; & dbname & &quot;.mdb&quot;<br>
<br>
Set Rs = New Recordset 'setting recordset<br>
sqlselect = &quot;select * from &quot; & tname & &quot;, db, adopenstatic, adlockoptimistic&quot;<br>
'rs.Open &quot;select * from &quot; & tname & &quot;, db, adopenstatic, adlockoptimistic&quot; 'not working<br>
Rs.Open &quot;select * from 1&quot;, Db, adOpenStatic, adLockOptimistic 'working<br>
<br>
'Rs.ActiveConnection = Db 'this was an alternative type of attempt but the following line<br>
'rs.Source = &quot;select * from &quot; tname 'gives an unexpected end of statement<br>
<br>
Set Text9.DataSource = Rs 'binding control<br>
Text9.DataField = &quot;High&quot;<br>

 

[calahans]

Hi thewhip,<br>
<br>
Looking at your code I see one odd thing:<br>
<br>
'rs.Open &quot;select * from &quot; & tname <b><i>& &quot;, db, adopenstatic, adlockoptimistic&quot; </b></i>'not working<br>
<br>
Why have you got all your db, cursor option and locking type within quotes. The system would read that as part of an SQL string.<br>
<br>
Am I reading it right?<br>
<br>
C

 

[thewhip]

Hmm. Originally it said <br>
rs.open sqlselect<br>
where as you can see sqlselect was a concatenated string. What would the appropriate format be to get tname into the rs.open statement? If I just remove the quotes, then it is no longer seen as a string, so I will have to remove the & for concatenation, and I am pretty sure I'll be getting an error. I can't try it right now, but I'll try it later today. I do not remember which of the many possible formats I have tried and which I have not tried.<br>
<br>
Rich

 

[thewhip]

It worked, thanks. Tha is why I put simple in the title, I figured I must have missed something simple. Maybe I've just been trying to do too many things at once. @-)