Import & action query procedure not working properly

[spiralmind]

I have written the following code to automate an import process (from a user-selected .txt file). When I first tested the form (and the module that went with it), the process worked as it was supposed to and all data was imported successfully. At the time, however, I had to manually check the temp tables used in the import process to remove any records left in the before and after the import process to avoid redundant storage of data (and the possibility of appending the same records to the desination table twice). I therefore created a set of delete queries to clear out these tables.
Now, although all of the queries I created work as they should when opened by hand, and although no errors seem to result when I run the module (no debug window or message boxes appearing until the final notification of the import's "successful" completion), no data reaches the destination table when I run this module.
Given the fact that I have successfully imported data by commenting out all the queries, and then running each one in the same sequence by hand, is it possible that timing issues are causing the delete queries to remove the data between the time it is imported and the time it should be appended, or are there other issues that I may be unaware of in how I have written this code?

the defective code:

Option Compare Database
Option Explicit
Dim frmSelect2Import As Form_frmSelect2Import
Dim FilePath As String
Dim FileName As String
'-----------------------------------------------------------------------------------------------------------------------
'-----------------------------------------------------------------------------------------------------------------------

Private Sub cmdImportAsGL_Click()
'Turn off built in warning messages
DoCmd.SetWarnings False
'Import data from the file selected on lstFileSelect
FilePath = Mid(PathName, 1, Len(PathName) - 5)
FileName = FilePath + Me.txtSelectedFile
DoCmd.TransferText acImportFixed, "specGL", "tmpGL1", FileName, False
'Append the imported data to permanent tables
'This data is filtered and reformatted by the queries in this process
DoCmd.OpenQuery "clearTmpGL1"
DoCmd.OpenQuery "clearTmpGL2"
DoCmd.OpenQuery "addGL1"
DoCmd.OpenQuery "chgEntitiesGL"
DoCmd.OpenQuery "chgAccountsGL"
DoCmd.OpenQuery "addGL2"
DoCmd.OpenQuery "clearTmpGL1"
DoCmd.OpenQuery "clearTmpGl2"
'Turn on built-in warning messages
DoCmd.SetWarnings True
'Inform the user that the import process is complete
MsgBox "Import Complete"
End Sub

'-----------------------------------------------------------------------------------------------------------------------
- Thankyou, Christian

 

[sanders720]

You might want to consider entering your queries in code, rather than referencing them from code.

dim sql as String
dim rs as Recordset

set rs=New ADOBB.Recordset

sql = "SELECT tblXXX.{field} FROM tblXXX WHERE {field}= " & integer variable &

OR

sql = "SELECT tblXXX.{field} FROM tblXXX WHERE {field}= '" & string variable & "' "

You have to add quotes within quotes if you want to use variables from a field name or form object, such as textboxes, listboxes or comboboxes.

To determine a query statement, make a query in Access, and then view the SQL string. Aside from tne quotes you need to add, this is what you will want your sql syntax to look like.

Next, you can perform the actual query by this...

Set rs=CurrentDb.OpenRecordset(sql)

rs.CursorLocation = adUseClient ' This is important to use before the Open...
rs.Open sql, CurrentProjectConnection, adOpenDynamic, adLockOptimistic

Use this to open the query into memory.



rs.MoveFirst
rs.MoveLast
rs.MoveNext
rs.MovePrevious

This will move you around in the query

rs.Update updates information

rs.Close
set rs=nothing

Do this at the end, even if you want to do another. You can use the same rs and sql variables and redefine them, or you can have several, such as sql1, sql2, rs1, rs2, etc.

 

[spiralmind]

Thankyou, Sanders, I've tried building my queries as strings within the code and doing so has worked beautifully. An excellent suggestion. (Though I should add that my actual implementation involved writing the strings as part of a RunSQL statement like so:

DoCmd.RunSQL &quot; << the sql string here >> &quot;
DoCmd.RunSQL &quot; << another string here >> &quot;

and so on...