Moving update queries to modules.

[paul123456]

I have a module that does mulitple things to allow a excel file to import. During the module process it calls a few different update queries. Can i just copy the update query in the module? or does it have to be in a update query? I have 2 many queries and i want to move as much as i can to modules and lock that down.?

Thanks, PAUL

 

[thecreator]

I usually distribute my applications as MDE's but am not adverse to using query objects for processing. The following code sits in a module. In this case I would call the function ExtractClasses to create/recreate the query object each time it is required, then use docmd.openquery "qryExtract_Classes" to actually run it.

That way, I can use query objects (as Access is designed to do) but am safe in the knowledge that if anybody does butcher a query that each time they run my code I am using the query that I actually created ... because my query is recreated or created each run.

When my database closes, I also delete all query objects and table links (I link tables on opening a database as well ... just in case somebody drops a table on me).

Sub ExtractClasses()
On Error GoTo ExtractClasses_Err

Dim dbs As Database, qdf As QueryDef
Dim strSQL As String, qryTag As String

' define query object name
qryTag = "qryExtract_Classes"

' refresh query collection
Set dbs = CurrentDb
dbs.QueryDefs.Refresh

' If query exists, delete it.
For Each qdf In dbs.QueryDefs
If qdf.Name = qryTag Then
dbs.QueryDefs.Delete qdf.Name
End If
Next qdf

strSQL = "Your query code here;"

' Create new QueryDef object.
Set qdf = dbs.CreateQueryDef(qryTag, strSQL)

ExtractClasses_Bye:
' release objects
qdf.Close
dbs.Close
Set qdf = Nothing
Set dbs = Nothing
Exit Sub

ExtractClasses_Err:
MsgBox Err.Description
Resume ExtractClasses_Bye

End Sub

Alternatively, you can create a SQL string and then docmd.runsql "MyString" to run your query. I like the first option better because it looks less messy in the "processing" function - just a one line call.

Cheers

 

[fabby1]

Hi

Is there away with the above code that you can create the query as a maketable query,

The above code just creates a Select query I assume

Regards

Phil

 

[pdldavis]

Hi, Here's a couple examples - Append, Update, Make Table.
Design your query, go to SQL view and copy the sql into you module. Preface the sql with DoCmd.RunSql and enclose the statement in " "

Hope that helps.


DoCmd.RunSQL "INSERT INTO tblCwrCnst ( CblNo )SELECT tblNewCables.CblNo FROM tblNewCables; "

DoCmd.RunSQL "UPDATE tblCwrCnst SET tblCwrCnst.PullPck = '" & "na" & "' " & _
"WHERE (((tblCwrCnst.PullPck) Is Null)); "

docmd.runsql "SELECT tblCWR.CblNo INTO mtNewTable
FROM tblCWR; "

 

[thecreator]

All the code does is to create a query "object" in your database ... what that query does is dependant on the SQL statement that you put into the strSQL line.

The easiset way is to create the query by design, go to the SQL view and copy the code to the string.

Hope this makes sense

 

[paul123456]

Im kinda confused about the difference between just running the code straight through the module rather then creating it then deleting it? what benefits do u get out of doing it this way?

Thanks, PAUL

 

[pdldavis]

Hi, personally I use the create query theCreator mentioned just to cut down on the number of stored queries - reduces clutter.

Action queries - make table, update and so on I keep in a module for safety reasons. Suppose you are developing a db in an accessible place and someone comes along and wonders what the query with the plus sign does. Could have some unintended consequences.

Hope that helps.