SQL with a Variable – not working

Topic

New Reply

This module is in MS Access 2007. I’ve written sql statement that uses a variable ‘strName’. The append is working but the ‘strName’ = “HP” is not. I tried using the quotes many different ways. There must be something fundamental I’m missing. I’ve put the statements in question in Red.
When I step thru the code, strName = “HP” as expected.
Thanks for your help!!!
Here is the code:

Code:

Private Sub ImportData()
On Error GoTo Err_ImportData
 
Dim strPath As String
Dim strFileNm As String
Dim db As Database, ws As Workspace
Dim strSQL As String
Dim strSQLYE As String
Dim strSQLLM As String
Dim Response As Variant
Dim txtMonth As String
Dim archivedb As String
Dim tblArchiveNM As String
Dim tblName As String
Dim curDate As Date
Dim rst1 As DAO.Recordset
Dim strName As String
 
 
Set db = CurrentDb
Set ws = Workspaces(0)
 
 
tblName = "HP Procurement"
VendorNM = "Dell"
 
strSQLDel = "DELETE [tblProcurement].* FROM [tblProcurement];"
 
strSQLDell = "INSERT INTO dbo_tblProcurement_Files ( PONbr, PODate, CustOrderNbr, ShipDate, Carrier, CarrierTrackingNbr, AU, Entity, CostCenter, InvoiceNbr, InvoiceDate, SKUNbr, SKUDescription, MfgName, SerialNbr, OrderPrice, SalesTax, TotalPrice, SoldTo, Quantity, Vendor, ImportDate )" & _
"SELECT [Dell Procurement].[PO Number], [Dell Procurement].[PO Date], [Dell Procurement].[Cust Order No], [Dell Procurement].[Ship Date], [Dell Procurement].Carrier, [Dell Procurement].[Carrier Tracking No], [Dell Procurement].AU, [Dell Procurement].Entity, [Dell Procurement].[Cost Center], [Dell Procurement].[Invoice Number], [Dell Procurement].[Invoice Date], [Dell Procurement].[SKU Number], [Dell Procurement].[SKU Description], [Dell Procurement].[Mfg Name], [Dell Procurement].[Serial No], [Dell Procurement].[Order Price], [Dell Procurement].[Sales Tax], [Dell Procurement].[Extended Price], [Dell Procurement].[Sold To], [Dell Procurement].Qty, ""Dell"" AS Vendor, '" & Date & "'" & _
"FROM [Dell Procurement];"
strSQLHP = "INSERT INTO dbo_tblProcurement_Files ( PONbr, PODate, CustOrderNbr, ShipDate, Carrier, CarrierTrackingNbr, AU, Entity, CostCenter, InvoiceNbr, InvoiceDate, SKUNbr, SKUDescription, MfgName, SerialNbr, OrderPrice, SalesTax, TotalPrice, SoldTo, Quantity, Vendor, ImportDate )" & _
"SELECT [HP Procurement].[PO Number], [HP Procurement].[PO Date], [HP Procurement].[Cust Order No], [HP Procurement].[Ship Date], [HP Procurement].Carrier, [HP Procurement].[Carrier Tracking No], [HP Procurement].AU, [HP Procurement].Entity, [HP Procurement].[Cost Center], [HP Procurement].[Invoice Number], [HP Procurement].[Invoice Date], [HP Procurement].[SKU Number], [HP Procurement].[SKU Description], [HP Procurement].[Mfg Name], [HP Procurement].[Serial No], [HP Procurement].[Order Price], [HP Procurement].[Sales Tax], [HP Procurement].[Extended Price], [HP Procurement].[Sold To], [HP Procurement].Qty, ""HP"" AS Expr1, '" & Date & "'" & _
"FROM [HP Procurement];"
strSQLInsight = "INSERT INTO dbo_tblProcurement_Files ( PONbr, PODate, CustOrderNbr, ShipDate, Carrier, CarrierTrackingNbr, AU, Entity, CostCenter, InvoiceNbr, InvoiceDate, SKUNbr, SKUDescription, MfgName, SerialNbr, OrderPrice, SalesTax, TotalPrice, SoldTo, Quantity, Vendor, ImportDate )" & _
"SELECT [Insight Procurement].[PO Number], [Insight Procurement].[PO Date], [Insight Procurement].[Cust Order No], [Insight Procurement].[Ship Date], [Insight Procurement].Carrier, [Insight Procurement].[Carrier Tracking No], [Insight Procurement].AU, [Insight Procurement].Entity, [Insight Procurement].[Cost Center], [Insight Procurement].[Invoice Number], [Insight Procurement].[Invoice Date], [Insight Procurement].[SKU Number], [Insight Procurement].[SKU Description], [Insight Procurement].[Mfg Name], [Insight Procurement].[Serial No], [Insight Procurement].[Order Price], [Insight Procurement].[Sales Tax], [Insight Procurement].[Extended Price], [Insight Procurement].[Sold To], [Insight Procurement].Qty, ""Insight"" AS Expr1, '" & Date & "'" & _
"FROM [Insight Procurement];"
 
[COLOR=red]strSQLVendor = "INSERT INTO dbo_tblProcurement_Files ( PONbr, PODate, CustOrderNbr, ShipDate, Carrier, CarrierTrackingNbr, AU, Entity, CostCenter, InvoiceNbr, InvoiceDate, SKUNbr, SKUDescription, MfgName, SerialNbr, OrderPrice, SalesTax, TotalPrice, SoldTo, Quantity, Vendor, ImportDate )" & _[/COLOR]
[COLOR=red]"SELECT tblProcurement.[PO Number], tblProcurement.[PO Date], tblProcurement.[Cust Order No], tblProcurement.[Ship Date], tblProcurement.Carrier, tblProcurement.[Carrier Tracking No], tblProcurement.AU, tblProcurement.Entity, tblProcurement.[Cost Center], tblProcurement.[Invoice Number], tblProcurement.[Invoice Date], tblProcurement.[SKU Number], tblProcurement.[SKU Description], tblProcurement.[Mfg Name], tblProcurement.[Serial No], tblProcurement.[Order Price], tblProcurement.[Sales Tax], tblProcurement.[Extended Price], tblProcurement.[Sold To], tblProcurement.Qty, '" & strName & "', #" & Date & "# " & "FROM tblProcurement;"[/COLOR]
[COLOR=black]'strSQLVendor = "INSERT INTO dbo_tblProcurement_Files ( PONbr, PODate, CustOrderNbr, ShipDate, Carrier, CarrierTrackingNbr, AU, Entity, CostCenter, InvoiceNbr, InvoiceDate, SKUNbr, SKUDescription, MfgName, SerialNbr, OrderPrice, SalesTax, TotalPrice, SoldTo, Quantity, Vendor, ImportDate )" & _[/COLOR]
[COLOR=black]"SELECT tblProcurement.[PO Number], tblProcurement.[PO Date], tblProcurement.[Cust Order No], tblProcurement.[Ship Date], tblProcurement.Carrier, tblProcurement.[Carrier Tracking No], tblProcurement.AU, tblProcurement.Entity, tblProcurement.[Cost Center], tblProcurement.[Invoice Number], tblProcurement.[Invoice Date], tblProcurement.[SKU Number], tblProcurement.[SKU Description], tblProcurement.[Mfg Name], tblProcurement.[Serial No], tblProcurement.[Order Price], tblProcurement.[Sales Tax], tblProcurement.[Extended Price], tblProcurement.[Sold To], tblProcurement.Qty, " & "'" & strName & "','" & Date & " '" & "FROM tblProcurement;"[/COLOR]
 
' Set MaxLocksPerFile.
DBEngine.SetOption dbMaxLocksPerFile, 200000
 
curDate = Date
 
Set rst1 = db.OpenRecordset("tblVendorData", dbOpenTable)
rst1.MoveFirst
 
Do While Not rst1.EOF
With rst1
strPath = !VendorLocation
strFileNm = !VendorFileName
strPathBackup = !VendorBackup
[COLOR=red]strName = !VendorName[/COLOR]
 
[COLOR=red]CurrentDb().Execute strSQLVendor, dbFailOnError ' 030320111 variable strName did not work[/COLOR]
FileDate = FileLastModified(strPath & strFileNm)
If FileDate = curDate Then
'MsgBox FileDate & " is equal to " & curDate
'deletes data from the master files
CurrentDb().Execute strSQLDel, dbFailOnError
DoCmd.TransferText acImportDelim, "Hp_asset_management Import", "tblProcurement", strPath & strFileNm
If strName = "HP" Then
CurrentDb().Execute strSQLHP, dbFailOnError
Else
If strName = "Dell" Then
CurrentDb().Execute strSQLDell, dbFailOnError
Else
CurrentDb().Execute strSQLInsight, dbFailOnError
End If
End If
 
 
'CurrentDb().Execute strSQLVendor, dbFailOnError ' 030320111 variable strName did not work
 
strNewFile = Left(strFileNm, InStr(strFileNm, ".") - 1) & " " & Format(FileDate, "mm dd yyyy") & ".csv"
strOrig = strPath & strFileNm
strDest = strPathBackup & strNewFile
Name strOrig As strDest 'works and renames
 
End If
 
rst1.MoveNext
End With
Loop
 
 
 
MsgBox "Done!"
 
 
Exit_ImportData:
Exit Sub
 
Err_ImportData:
MsgBox Err.Description
Resume Exit_ImportData
 
End Sub

Reply | Quote

Replies

I’m going to move this to the Databases forum, since that is where most of the Access gurus can be found.

Reply | Quote

You need to put the line
strSQLVendor = “INSERT INTO ….
after the line
strName = !VendorName

Otherwise, you are just inserting blanks.

Reply | Quote

To amplify the previous reply, I think that you do things in the wrong order.

When you define strSQLVendor, and include strName in it via : ‘” & strName & “‘, what you are putting into the SQL is the value of StrName rather than the variable itself.
So when you assign a value to strName later in the code it is too late. The newly assigned value does not find its way back to the SQL.

So you need to move the definition of strSQLVendor inside the loop, after you have assigned a value to strName.

Reply | Quote

THANKS so much!!! Now that I’m back from vacation, I changed the code and it works! Thanks for the fix and for the explanation!

Reply | Quote