VBA: Convert SQL query in VBA (insert into) 2

[DajTwo]

Access 2003, front end / back end config.

I need to convert an APPEND query in VBA code and the concept of punctiation in VBA still eludes me other than I know that ' ' is for text, and # # is for dates.

Can someone tell me where I can get tutotials showing how to construct select, insert into etc.. statements with punctuation?. I jsut bought MS Offoce Access 2003 bible from Wiley, Beginning Access 2003 VBA form and Access 2003 Programmer's reference both from Wrox and I cant find what I am looking for.

Thanks in advance..

 

[SkipVought]

Hi,

Your "punctuations" are really DELIMITERS in the SQL code:

' (apostrophy) to delimit TEXT literals
# to delimit DATE literals and convert the literal to a REAL DATE.

"I need to convert an APPEND query in VBA code.."

Convert FROM what TO what?


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DajTwo]

Hi,

I looked in teh books for 'delimmiters' and althouth there are examples, there is no tutorial to understand the basics of how to properly use the delimiters.

I constructed the query in the visual interface, then copied the SQL statement. That is what I need to be 'converted' in a form that can be understood by VBA.


[ccan] is text
[audit_#] is numeric
[audit_no] is numeric
[audit_sub_no] is numeric
[contract_id] is text
[asset_id] is text
cmbvalue is text
var_sub_audit is numeric

Dim rsAssetUpd As Recordset

Set rsAssetUpd = db.OpenRecordset("INSERT INTO [tbl_extra_asset]
("[ccan]", "[audit_#]", "[audit_sub_#]","[contract_id]", "[asset_id]")
SELECT [tbl_customer].[ccan], [tbl_audit].[audit_no], [tbl_audit].[audit_sub_#], [tbl_contract].[contract_id], [tbl_asset].[asset_id] FROM [tbl_customer] LEFT JOIN (([tbl_audit] LEFT JOIN [tbl_contract] ON [tbl_audit].[ccan] = [tbl_contract].[ccan])
LEFT JOIN [tbl_asset] ON [tbl_contract].[contract_id] = [tbl_asset].[contract_id]) ON [tbl_customer].[ccan] = [tbl_audit].[ccan]
WHERE ((([tbl_customer].[ccan])='" & cmbvalue & "')AND (([tbl_audit].[audit_sub_#])=" & var_sub_audit & ) AND (([tbl_asset].[asset_id]) Is Not Null));


Can you point out a resource that I can look at?

Thanks

 

[SkipVought]

Don't know a good resource. Just Tek-Tips Access Forums and other database forums.

    Dim rsAssetUpd As Recordset, sSQL As String
    
    sSQL = "INSERT INTO [tbl_extra_asset]"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "([ccan], [audit_#], [audit_sub_#],[contract_id], [asset_id])"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "SELECT"
    sSQL = sSQL & "  [tbl_customer].[ccan]"
    sSQL = sSQL & ", [tbl_audit].[audit_no]"
    sSQL = sSQL & ", [tbl_audit].[audit_sub_#]"
    sSQL = sSQL & ", [tbl_contract].[contract_id]"
    sSQL = sSQL & ", [tbl_asset].[asset_id]"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "FROM        [tbl_customer] "
    sSQL = sSQL & "LEFT JOIN (([tbl_audit] "
    sSQL = sSQL & "LEFT JOIN   [tbl_contract]"
    sSQL = sSQL & "  ON [tbl_audit].[ccan]            = [tbl_contract].[ccan])"
    sSQL = sSQL & "LEFT JOIN   [tbl_asset]"
    sSQL = sSQL & "  ON [tbl_contract].[contract_id]  = [tbl_asset].[contract_id])"
    sSQL = sSQL & "  ON [tbl_customer].[ccan]         = [tbl_audit].[ccan]"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "WHERE ((([tbl_customer].[ccan])    ='" & cmbvalue & "')"
    sSQL = sSQL & "  AND (([tbl_audit].[audit_sub_#]) =" & var_sub_audit & ")"
    sSQL = sSQL & "  AND (([tbl_asset].[asset_id])    Is Not Null));"

    
    Set rsAssetUpd = Db.OpenRecordset(sSQL)

I always organize my SQL like this. Makes it easier to understand and maintain.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DajTwo]

Thanks Skip,

Never seen code like this. Looks logical to me..

I get a Illegal Operation 3219 error message at

Set rsAssetUpd = db.OpenRecordset(sSQL)

Ill search to see what could cause this..

 

[DajTwo]

In the local debug window

sSQL=

"INSERT INTO [tbl_extra_asset]
([ccan], [audit_#], [audit_sub_#],[contract_id], [asset_id])
SELECT [tbl_customer].[ccan], [tbl_audit].[audit_no], [tbl_audit].[audit_sub_#], [tbl_contract].[contract_id], [tbl_asset].[asset_id]
FROM [tbl_customer] "

Goes no further..

 

[DajTwo]

Ship,

I inserted a breakpoint and cycled throuhgh to the line

Set rsAssetUpd = db.OpenRecordset(sSQL) no problem although it could be expected as sSQL is a string and would not compile until sSQL is executed.

The local debug window stopped where previously mentionned.

 

[SkipVought]

Do you know for SURE that your SQL executes correctly? I rarely compose SQL and execute. Most often, I construct my SQL in a QBE grid, to assure that it will execute correctly.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DajTwo]

Skip,

I can execute the query composed in the visual interface and it runs as intended. It appends the records selected in the where statement.

Is the fact that the sSQL debug window stops where it does relevant?

Daj

 

[SkipVought]

Please COPY the SQL that correctly executes, and POST here.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dhookom]

If you have the DAO library referenced, you should be able to use:

CurrentDb.Execute sSQL

or even

DoCmd.RunSQL sSQL

Duane
Hook'D on Access
MS Access MVP

 

[DajTwo]

Skip,

As requested, this is the SQL code of the query. I added one where statement compared to my previous post.

The field values below are stored in the following variables

ccan = cmbvalue
audit_no = var_audit
audit_sub_# = var_sub_audit

INSERT INTO tbl_extra_asset ( ccan, [audit_#], [audit_sub_#], contract_id, asset_id )
SELECT tbl_customer.ccan, tbl_audit.audit_no, tbl_audit.[audit_sub_#], tbl_contract.contract_id, tbl_asset.asset_id
FROM tbl_customer LEFT JOIN ((tbl_audit LEFT JOIN tbl_contract ON tbl_audit.ccan = tbl_contract.ccan) LEFT JOIN tbl_asset ON tbl_contract.contract_id = tbl_asset.contract_id) ON tbl_customer.ccan = tbl_audit.ccan
WHERE (((tbl_customer.ccan)="295595") AND ((tbl_audit.audit_no)=54) AND ((tbl_audit.[audit_sub_#])=11) AND ((tbl_asset.asset_id) Is Not Null));


Thanks!!

If I did not say it before, I really appreciate the time and expertise of the users on this forum.

 

[DajTwo]

I replaced the variables with the actual data in the vba code and I obtained the same error so I eliminated error on that side.

If I did not say it before, I really appreciate the time and expertise of the users on this forum.

 

[PHV]

What is YOUR actual VBA code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DajTwo]

This is the entire code of the event.

All works well and as intended until the
Set rsAssetUpd = db.OpenRecordset(sSQL) code.


Option Compare Database
Option Explicit

Private Sub Command10_Click()

' Set variables & values
Dim cmbvalue As String 'For CCAN
Dim dateval As Date 'For date
Dim var_audit As Long 'For audit_#
Dim var_sub_audit As Long 'For audit_sub_#
Dim strSQL As String 'For retrieving the audit_#
Dim stDocName As String 'For menu changing
Dim stLinkCritaria As String 'For menu changing

'On Error GoTo ErrorHandler

cmbvalue = Me.Combo8.Value
dateval = Now()
stDocName = "menu_audit"

' Select the table tbl_audit_gener, add the new record, create the audit_# and close the form.
DoCmd.OpenTable "tbl_audit_gener"
DoCmd.GoToRecord , , acNewRec
ccan = cmbvalue
audit_generated_date = dateval

DoCmd.Save
DoCmd.Close
DoCmd.Close acForm, "audit_#_generator"

' Select the table tbl_audit_gener, retrieve the audit_# record based on the variables

Dim db As Database
Dim rsAudNum As Recordset

Set db = CurrentDb
Set rsAudNum = db.OpenRecordset("tbl_audit_gener")

var_audit = DLookup("[audit_#]", "[tbl_audit_gener]", "ccan='" & cmbvalue & "' AND audit_generated_date=#" & dateval & "#")

' Select the table tbl_audit, create a new record, and use the variables as default for the field

Dim rsAudit As Recordset
Set db = CurrentDb
Set rsAudit = db.OpenRecordset("tbl_audit")

rsAudit.AddNew
rsAudit!ccan = cmbvalue
rsAudit!audit_prepost_date = dateval
rsAudit!audit_no = var_audit
rsAudit!audit_status = "Pre-Posted"
rsAudit!audit_status_date = dateval
rsAudit.Update

' Select tbl_audit, retrieve the audit_sub_# and store in a variable to put into tbl_extra_asset

Dim rsAudSubNum As Recordset

Set db = CurrentDb
Set rsAudSubNum = db.OpenRecordset("tbl_audit")

var_sub_audit = DLookup("[audit_sub_#]", "[tbl_audit]", _
"ccan='" & cmbvalue & "' AND audit_no =" & var_audit & " AND audit_prepost_date=#" & dateval & "#")

' Select the table tbl_extra_asset, select all assets subject to the pre-audit generated
' then append these assets in the tbl_extra_assets where values are equal to the variables accumulated.

Dim rsAssetUpd As Recordset
Dim sSQL As String

sSQL = "INSERT INTO [tbl_extra_asset]"
sSQL = sSQL & vbLf
sSQL = sSQL & "([ccan], [audit_#], [audit_sub_#],[contract_id], [asset_id])"
sSQL = sSQL & vbLf
sSQL = sSQL & "SELECT"
sSQL = sSQL & " [tbl_customer].[ccan]"
sSQL = sSQL & ", [tbl_audit].[audit_no]"
sSQL = sSQL & ", [tbl_audit].[audit_sub_#]"
sSQL = sSQL & ", [tbl_contract].[contract_id]"
sSQL = sSQL & ", [tbl_asset].[asset_id]"
sSQL = sSQL & vbLf
sSQL = sSQL & "FROM [tbl_customer] "
sSQL = sSQL & "LEFT JOIN (([tbl_audit] "
sSQL = sSQL & "LEFT JOIN [tbl_contract]"
sSQL = sSQL & " ON [tbl_audit].[ccan] = [tbl_contract].[ccan])"
sSQL = sSQL & "LEFT JOIN [tbl_asset]"
sSQL = sSQL & " ON [tbl_contract].[contract_id] = [tbl_asset].[contract_id])"
sSQL = sSQL & " ON [tbl_customer].[ccan] = [tbl_audit].[ccan]"
sSQL = sSQL & vbLf
sSQL = sSQL & "WHERE ((([tbl_customer].[ccan]) ='" & 215330 & "')"
sSQL = sSQL & " AND (([tbl_audit].[audit_no]) =" & 92 & ")"
sSQL = sSQL & " AND (([tbl_audit].[audit_sub_#]) =" & 45 & ")"
sSQL = sSQL & " AND (([tbl_asset].[asset_id]) Is Not Null));"

Set rsAssetUpd = db.OpenRecordset(sSQL)

MsgBox "You have created an audit for CCAN:" & cmbvalue & "and the audit number is: " & var_audit & ". Take note of the audit number.", vbOKOnly, "Audit Number Confirmation"

rsAudNum.Close
rsAudit.Close
rsAudSubNum.Close
rsAssetUpd.Close

' Go to menu_audit

DoCmd.OpenForm stDocName, , , stLinkCritaria

ExitHere:
Exit Sub

'ErrorHandler:

'If Err.Number = 94 Then
'MsgBox "Please select a Customer Name and Audit Type before issuing an audit report", vbOKOnly, "Customer Select Error"
'Else
'MsgBox "Take note of the error number and description " & Err.Number & vbCrLf & Err.Description & " Contact XXX at XXX-XXX-XXXX with that information", vbOKOnly, "Unplanned Error"

'End If

'Resume ExitHere

End Sub



If I did not say it before, I really appreciate the time and expertise of the users on this forum.

 

[PHV]

As pointed out by Duane use Execute instead of OpenRecordset for an action query.

Furthermore your code is messy, sorry.
You assign several times db, you open directly a table, you create a recordset (rsAudNum) for nothing, ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DajTwo]

PHV

I unfortunately know that my code is messy. I built this by section adding new ones at the end of one that is working. My knowledge of VBA is very limited and not to bore you with my problems I was given a project that I am not qualified to do despite my objections and my timeline is very short.

My intent is to clean it up as soon as this part is working.

Very sorry if I waisted your time.

Daj



If I did not say it before, I really appreciate the time and expertise of the users on this forum.