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;