Create a dynamic logfile from Sql statement records 1

[JustKev]

Im a newbie so bear with me, I have several versions of a database with 30 odd tables in each. Each database has records that must be completed and so I create standard queries that are then copied to each Db instance.

The problem is that these Query reports need constantly updating amending etc (bosses eh!), I would like to create a single log file (table) that is appended to by VB but the SQL statement is held in a table, that can easily be amended at the table level rather than keep having to update the VB in the form.

So Im looking for a simple example of looping through the table and executing each SQL statement if possible.

 

[Remou]

As in:

Dim rs As DAO.Recordset
Set rs=CurrentDB.Openrecordset("Select * From tblSQL")
Do While Not rs.EOF
   strSQL=rs!SQLText
   CurrentDB.Execute strSQL, dbFailOnError
   rs.Movenext
Loop

 

[JerryKlmns]

Store your SQL statements in a memo field and the query name to a normal text field. For reading them use a recordset loop. On every record, use a DAO.QueryDef object to point to each query 's name and assign the SQL statement read to the .SQL property. Close properly the recordset and you ready.

 

[JustKev]

Thanks for your help, Im very new so stumbling along really, the idea is to be able to add records to the Statement so it is dynamic the sql reference table is:

Key(autonumber) StateName(text) IntegStatement(memo)

and it is populating a logfile with generic fields, an example of the SQL statement is:

INSERT INTO minINTEGRITY ( FIELD1, F1KEY,FIELD2, F2KEY, FIELD3, F3KEY, FIELD4, F4KEY, RepName, REMARKS ) SELECT minLOCHIERARCHY.LocationCode,'minLOCHIERARCHY.LocationCode' AS F1KEY, minLOCATIONS.Description,'minLOCATIONS.Description' AS F2KEY, minLOCHIERARCHY.PARENT, 'minLOCHIERARCHY.PARENT' AS F3KEY, minLOCHIERARCHY.SYSTEM,'minLOCHIERARCHY.SYSTEM' AS F4KEY, 'Locations_Unnamed' AS RepName, 'Check of Locations in the SAP Hierarchy that do not start with the SAP Site Code' AS REMARKS FROM minLOCHIERARCHY INNER JOIN minLOCATIONS ON minLOCHIERARCHY.LocationCode = minLOCATIONS.LocationCode
WHERE (((minLOCHIERARCHY.LocationCode) Not Like ((SELECT minMAXVARS.VARVALUE FROM minMAXVARS WHERE (minMAXVARS.VARNAME)='SITECODE')) & '*') AND ((minLOCHIERARCHY.PARENT) Like ((SELECT minMAXVARS.VARVALUE FROM minMAXVARS WHERE (minMAXVARS.VARNAME)='SITECODE'))
& '*') AND ((minLOCHIERARCHY.SYSTEM)='PRIMARY'))

So looping through the statements to create inserts into the logfile is the ultimate goal.

I appreciate your help.

 

[JustKev]

Thanks Remou

Finally got my head round it, and it works fine. I have now got a Data validation table that can be used for reporting and exporting into s spreadsheet.



Dim rs As DAO.Recordset
Dim strSql As String

Set rs = CurrentDb.OpenRecordset("Select IntegStatement FROM minIntStatements;")
Do While Not rs.EOF
strSql = rs!IntegStatement
CurrentDb.Execute strSql
rs.MoveNext
Loop

rs.Close
Set rs = Nothing