How to run Update statements stored in a table

[DougJrs]

Good Morning Everyone!

I have an access database that has two tables in it (a data table and
an update table.) The update table has update SQL statements in it
that update the data table. How do I extract the update statements
from the update table and run them?

The update table has the actual SQL statement stored in a text field
(ie "update data ...")

Thanks,
Doug

 

[Rick Brandt]

Good Morning Everyone!

I have an access database that has two tables in it (a data table and
an update table.) The update table has update SQL statements in it
that update the data table. How do I extract the update statements
from the update table and run them?

The update table has the actual SQL statement stored in a text field
(ie "update data ...")

Thanks,
Doug

Dim sql as String
sql = SomethingToRetrieveSQLRecord
CurrentDB.Execute sql, dbFailOnError

SomethingToRetrieveSQLRecord can be DLookup(), using a Recordset, or a
custom function designed specifically to do the job. Obvioulsy the code
needs some way on knowing *which* record in the update table that needs to
be executed.

 

[Jerry Whittle]

Something like this will work; however, you are going to have to figure out
which how to pick out the correct record to run.

Also it won't run with SELECT statements. Needs to be an action query.

Public Sub fRunSQLs()
Dim SQL As String

SQL = DLookup("[SQLs]", "SQL_STATEMENTS")
DoCmd.RunSQL SQL

End Sub

Why not just save the SQL statements as queries? Then you can call on them
by name and even run SELECT queries easier.

 

[DougJrs]

I thought about the Query idea, but I have 14,372 updates that need to
run. I am basically doing a data conversion.

I want to run the queries in the same order that they are in the
update table, so could the code to select them be as simple as "select
update from update"?

Thanks,
Doug

 

[Rick Brandt]

I thought about the Query idea, but I have 14,372 updates that need to
run. I am basically doing a data conversion.

I want to run the queries in the same order that they are in the
update table, so could the code to select them be as simple as "select
update from update"?

Thanks,
Doug

You would use a simple SELECT statement to open a Recordset and then would
loop through the Recordset executing each statement as you go.

Unfortiunately in database tables thgeere is no such thing as "the same
order" as tables have no inherant order. You need some kind of field in the
table that you can explicitly sort on in your SELECT statement or you cannot
be guaranteed what order the records will come back in.

 

[Rick Brandt]

I thought about the Query idea, but I have 14,372 updates that need to
run. I am basically doing a data conversion.

I want to run the queries in the same order that they are in the
update table, so could the code to select them be as simple as "select
update from update"?

Thanks,
Doug

How many tables are you updating?

 

[IRS Intern]

I just use this concept called 'chapters' where I name all of my
action queries like this:


QA01_NewGroup_101
QA02_GroupLetter_201
QA02_GroupLetter_202


It isn't the prettiest code; I'm going to rewrite it as a CLR sproc
sometime soon; I just haven't gotten around to it



Option Compare Database
Option Explicit

Public cnn As ADODB.Connection

Public Enum chapPhase
chapDDL = 0 'L - DDL query
chapFlash = 1 'F - flash query
chapDelete = 3 'D - delete query
chapAppend = 4 'A - append query
chapUpdate = 5 'U - update query
chapSelect = 6 'S - doesn't really 'do' anythign; this is a way to
keep queries on top of queries organized
chapError = 7 'E - execute a query; if rst.fields(0).value = True
then stop processing
End Enum

Public Function FlashAllActiveQueries()
On Error GoTo errHandler

Set cnn = New ADODB.Connection
cnn.ConnectionString = CurrentProject.Connection.ConnectionString
cnn.Open

Dim D_START As Date
Dim D_END As Date
Dim I_LEN As Integer
Dim S_Msg As String
Dim I As Byte

D_START = Now()
For I = 1 To 99
Call ProcessChapter(I)
Next I
D_END = Now()

I_LEN = DateDiff("S", D_START, D_END)

S_Msg = "Processing completed " & vbCrLf & " Number of seconds: " &
I_LEN
Debug.Print Now() & " : " & S_Msg

cleanExit:
Exit Function
errHandler:
MsgBox Err.Number & " - " & Err.Description, vbOKOnly
Resume Next
End Function

Public Sub ProcessChapter(chapterMask As Byte)
On Error GoTo errHandler

If chapterMask < 0 Or chapterMask > 99 Then
MsgBox "ChapterMask must be 2 characters; and it can only contain
characters 0-9 and A-F."
End
End If

Dim S_ChapterWithX As String
S_ChapterWithX = "'QX" & Right("0" & chapterMask, 2) & "'"

MsgBox S_ChapterWithX, vbOKOnly



Dim S_DDLLike As String
Dim S_FlashLike As String
Dim S_ImportLike As String
Dim S_DeleteLike As String
Dim S_UpdateLike As String
Dim S_AppendLike As String

S_DDLLike = "'QL" & Right(S_ChapterWithX, Len(S_ChapterWithX) - 3)
S_ImportLike = "'QI" & Right(S_ChapterWithX, Len(S_ChapterWithX) - 3)
S_FlashLike = "'QF" & Right(S_ChapterWithX, Len(S_ChapterWithX) - 3)
S_DeleteLike = "'QD" & Right(S_ChapterWithX, Len(S_ChapterWithX) - 3)
S_UpdateLike = "'QU" & Right(S_ChapterWithX, Len(S_ChapterWithX) - 3)
S_AppendLike = "'QA" & Right(S_ChapterWithX, Len(S_ChapterWithX) - 3)

'go through and run all of the 'Flash queries' for a particular
chapter..
'then
'go through and run all of the 'Update queries' for a particular
chapter..
Dim rst As New ADODB.Recordset
rst.Open "Select [name] from sysobjects where xtype = 'P' AND
LEFT([name], 4) = " & S_DDLLike & " ORDER BY [NAME]", cnn
Do Until rst.EOF
FlashDDLThisQuery (rst!Name)
rst.MoveNext
Loop

Set rst = Nothing
Set rst = New ADODB.Recordset

rst.Open "Select [name] from sysobjects where xtype = 'P' AND
LEFT([name], 4) = " & S_ImportLike & " ORDER BY [NAME]", cnn
Do Until rst.EOF
FlashImportThisQuery (rst!Name)
rst.MoveNext
Loop

Set rst = Nothing
Set rst = New ADODB.Recordset

rst.Open "Select [name] from sysobjects where xtype = 'V' AND
LEFT([name], 4) = " & S_FlashLike & " ORDER BY [NAME]", cnn
Do Until rst.EOF
FlashThisQuery (rst!Name)
rst.MoveNext
Loop

Set rst = Nothing
Set rst = New ADODB.Recordset

rst.Open "Select [name] from sysobjects where xtype = 'P' AND
LEFT([name], 4) = " & S_DeleteLike & " ORDER BY [NAME]", cnn

Do Until rst.EOF
FlashDeleteThisQuery (rst!Name)
rst.MoveNext
Loop

Set rst = Nothing
Set rst = New ADODB.Recordset

rst.Open "Select [name] from sysobjects where xtype = 'P' AND
LEFT([name], 4) = " & S_UpdateLike & " ORDER BY [NAME]", cnn
'MsgBox S_UpdateLike, vbOKOnly
Do Until rst.EOF
FlashUpdateThisQuery (rst!Name)
rst.MoveNext
Loop

Set rst = Nothing
Set rst = New ADODB.Recordset

rst.Open "Select [name] from sysobjects where xtype = 'P' AND
LEFT([name], 4) = " & S_AppendLike & " ORDER BY [NAME]", cnn
'MsgBox S_AppendLike, vbOKOnly
Do Until rst.EOF
FlashAppendThisQuery (rst!Name)
rst.MoveNext
Loop

Set rst = Nothing


cleanExit:
DoCmd.SetWarnings True
Exit Sub
failexit:
GoTo cleanExit
errHandler:
MsgBox Err.Number & " - " & Err.Description, vbOKOnly
Resume Next
End Sub

Public Sub FlashThisQuery(strQry As String)
On Error GoTo errHandler

Dim strTbl As String
Dim S_result As String
Dim B_tblExists As Boolean

strTbl = "T" & Right(strQry, Len(strQry) - 1)

'Dim S_TABLEDELETE As String
'S_TABLEDELETE = "if exists (select 1 from sysobjects where name = '"
strTbl & "') " & vbCrLf & _
' "BEGIN " & vbCrLf & _
' "DROP TABLE " & strTbl & vbCrLf & _
' " END"
'
'
'DoCmd.RunSql S_TABLEDELETE, False

Dim strSql As String
strSql = " IF EXISTS(Select 0 from sysobjects where id =
OBJECT_ID('dbo." & strTbl & "')) "
strSql = vbCrLf & strSql & " BEGIN "
strSql = vbCrLf & strSql & " DROP TABLE dbo.[" & strTbl & "] "
strSql = vbCrLf & strSql & " End "
strSql = vbCrLf & strSql & " Select *, GETDATE() AS DateFlashed INTO
dbo.[" & strTbl & "] FROM [" & strQry & "]"

'DoCmd.RunSQL "EXEC " & strQry
DoCmd.RunSQL strSql

cleanExit:
Exit Sub
errHandler:
MsgBox Err.Number & " - " & Err.Description, vbOKOnly
Resume Next
Resume
End Sub

Public Sub FlashDDLThisQuery(strQry As String)
On Error GoTo errHandler

If Left(strQry, 2) <> "qL" Then
MsgBox "DDLFlash Name should look like 'QL'"
End
End If

'DoCmd.RunSQL "EXEC " & strQry
DoCmd.RunSQL strQry

cleanExit:
Exit Sub
errHandler:
MsgBox Err.Number & " - " & Err.Description, vbOKOnly
Resume Next
Resume
End Sub

Public Sub FlashInsertThisQuery(strQry As String)
On Error GoTo errHandler

If Left(strQry, 2) <> "QI" Then
MsgBox "InsertFlash Name should look like 'QI'"
End
End If

'DoCmd.RunSQL "EXEC " & strQry
DoCmd.RunSQL strQry

cleanExit:
Exit Sub
errHandler:
MsgBox Err.Number & " - " & Err.Description, vbOKOnly
Resume Next
Resume
End Sub

Public Sub FlashDeleteThisQuery(strQry As String)
On Error GoTo errHandler

If Left(strQry, 2) <> "qd" Then
MsgBox "Delete QueryFlash Name should look like 'QD'"
End
End If

'DoCmd.RunSQL "EXEC " & strQry
DoCmd.RunSQL strQry

cleanExit:
Exit Sub
errHandler:
MsgBox Err.Number & " - " & Err.Description, vbOKOnly
Resume Next
Resume
End Sub

Public Sub FlashUpdateThisQuery(strQry As String)
On Error GoTo errHandler

If Left(strQry, 2) <> "qu" Then
MsgBox "Standard Update Query should be named like 'QU'"
End
End If

'DoCmd.RunSQL "EXEC " & strQry
DoCmd.RunSQL strQry

cleanExit:
Exit Sub
errHandler:
MsgBox Err.Number & " - " & Err.Description, vbOKOnly
Resume Next
End Sub

Public Sub FlashImportThisQuery(strQry As String)
On Error GoTo errHandler

If Left(strQry, 2) <> "QI" Then
MsgBox "Standard Import Query should be named like 'QI'"
End
End If

'DoCmd.RunSQL "EXEC " & strQry
DoCmd.RunSQL strQry

cleanExit:
Exit Sub
errHandler:
MsgBox Err.Number & " - " & Err.Description, vbOKOnly
Resume Next
End Sub

Public Sub FlashAppendThisQuery(strQry As String)
On Error GoTo errHandler

If Left(strQry, 2) <> "qa" Then
MsgBox "Standard Append Query should be named like 'qa'"
End
End If

'DoCmd.RunSQL "EXEC " & strQry
DoCmd.RunSQL strQry

cleanExit:
Exit Sub
errHandler:
MsgBox "Append Failure: " & strQry & vbCrLf & Err.Number & " - " &
Err.Description, vbOKOnly
Resume Next
End Sub

 

[John W. Vinson]

Good Morning Everyone!

I have an access database that has two tables in it (a data table and
an update table.) The update table has update SQL statements in it
that update the data table. How do I extract the update statements
from the update table and run them?

The update table has the actual SQL statement stored in a text field
(ie "update data ...")

You can use a Recordset and either select a single row, or loop through rows;
when you extract the string use it to create a Querydef object. E.g.

Dim db As DAO.Database
Dim qd As DAO.Querydef
Dim rs As DAO.Recordset
Dim iCount As Integer
On Error GoTo Proc_Error
Set db = CurrentDb
Set rs = db.OpenRecordset("UpdateTable", dbOpenDynaset)
<code to loop or select a record in rs>
' create an unnamed "" querydef from the SQLString field in your table
Set qd = db.CreateQuerydef("", rs!SQLString)
qd.Execute dbFailOnError
iCount = qd.RecordsAffected
If iCount = 0 Then
<do something appropriate for the query doing nothing>
Else
<do nothing, or tell the user how many records were updated>
End If
Set qd = Nothing
<rest of loop code>
rs.Close ' clean up after yourself
Set rs = Nothing
Proc_Exit:
Exit Sub
Proc_Error:
<handle error condition>
Resume Proc_Exit
End Sub

John W. Vinson [MVP]