Search for :

Access VB SQL - Useful VB SQL for Access Functions

In Access VB there are different types of code which needs to run
depending on :-

1. Whether the Query is Defined in the Query Section of your database
2. whether values need to be passed or received from the VB/SQL/Query


The code below gives working examples depending on whether the code or parameter passing is

required:-




Definitions required at top of VB or function
=================================================
Dim sQueryName As String
Dim strSQLSTMT As String
Dim strFN As String
Dim strSheetName As String
Dim db As DAO.Database
Dim qd As DAO.QueryDef

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

NO VALUES PASSED

VB to run SQL
====================
Dim strSQLSTMT As String
strSQLSTMT = "INSERT INTO datatable ( [no], turn, [isbn 13] )"
strSQLSTMT = strSQLSTMT & " SELECT s1.no, s1.turn, s1.[isbn 13]"
strSQLSTMT = strSQLSTMT & " FROM" & strSheetName & " AS s1;"
CurrentDb.Execute strSQLSTMT


VB to run defined Access Query
=================================
sQueryName = "UpdateTerritories"
Set qd = db.QueryDefs(sQueryName)
qd.Execute

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

VALUE RETURNED

VB to run SQL
================
'
' get number of rows loaded into reCcount
'
strSQLSTMT = "SELECT Count(*) FROM msysobjects m WHERE m.type=1"
strSQLSTMT = strSQLSTMT & " and m.name like '*ImportError*';"
Set rec = db.OpenRecordset(strSQLSTMT)
With rec
.MoveLast
.MoveFirst
recCount = .Fields(0)
.Close
End With

VB to run a defined Access Query
===================================
'
' get number of rows loaded into recCount
'
sQueryName = "DataCount"
Set qd = db.QueryDefs(sQueryName)
Set rst = qd.OpenRecordset()
recCount = rst.RecordCount
If recCount = 1 Then
With rst
.MoveFirst
""Me.txtRowsLoaded = CStr(.Fields("loaded"))
End With
Else
" MsgBox ("Error has occurred during spreadsheet load")
End If

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

VALUE PASSED

VB run an Accessed defined query which returns nothing
(note you cannot insert a table name only fields and values)
=================================================================
Dim sQueryName As String
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim pr As Currency
Set db = CurrentDb

pr = Me.listboxPrice.ItemData(listBoxproducts.ListIndex)
sQueryName = "Insert_Product"
Set qd = db.QueryDefs(sQueryName)
qd.Parameters("sheet") = strSheetName
qd.Execute

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@



Useful SQL Statements put here to copy to own page later


THIS DELETES ALL DATA WHERE DateCopied is more than 3 MONTHS ago
================================================================
DELETE *
FROM BackupDataTable
WHERE DateCopied < DateAdd("M", -3, Date());

THIS Inserts data into an existing table adds field DateCopied
==================================================================
INSERT INTO BackupDataTable ( F1, F2, [ISBN 10], AUTHOR, TITLE, [TITLE 2], [Sub tit], [SUB TIT 2], [PUB DATE], DateCopied )
SELECT DataTable.F1, DataTable.F2, DataTable.[ISBN 10], DataTable.AUTHOR, DataTable.TITLE, DataTable.[TITLE 2], DataTable.[Sub tit], DataTable.[SUB TIT 2], DataTable.[PUB DATE], date()
FROM DataTable;
    IP