Automatically Writing SQL From A Table

[smitstev]

Hello,



I am creating a database that needs to exclude many differnt factors (which will also need to be edited and changed on a regular basis). Is there anyway I can have a table which contains a description of the exception, and the SQL statement... From here I would like for access to automatcially generate the SQL statements based upon the list of the exceptions. This would leave me with a query that has left me with the exceptions.

Is this possible?

Any help would be greatly apprciated...

[Patrick.Grant01]

You could do something along these lines. Use an ADODB recordset to read the table and create as a string. Then use the SQL string to create another recordset.

Sample code below:
Dim rstCheckInvestment As ADODB.Recordset


Set rstCheckInvestment = New ADODB.Recordset
Set rstCheckInvestment.ActiveConnection = CurrentProject.Connection
rstCheckInvestment.CursorType = adOpenKeyset
rstCheckInvestment.LockType = adLockOptimistic
strSQL = "SELECT * FROM tblInvestment Where SharescopeId =" & ExternalKey
rstCheckInvestment.Source = strSQL
rstCheckInvestment.Open

Loop through the recordset using Do While not rst.Eof and rst.MoveNext and rst.Close. etc.

I think ADO is documented in the VBA help - but its hard to read...