How to fix anything

Search for :

Google this page is not a duplicate

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


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



VB to run SQL
Dim strSQLSTMT As String
strSQLSTMT = "INSERT INTO datatable ( [no], turn, [isbn 13] )"
strSQLSTMT = strSQLSTMT & " SELECT, 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)



VB to run SQL
' get number of rows loaded into reCcount
strSQLSTMT = "SELECT Count(*) FROM msysobjects m WHERE m.type=1"
strSQLSTMT = strSQLSTMT & " and like '*ImportError*';"
Set rec = db.OpenRecordset(strSQLSTMT)
With rec
recCount = .Fields(0)
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
""Me.txtRowsLoaded = CStr(.Fields("loaded"))
End With
" MsgBox ("Error has occurred during spreadsheet load")
End If



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


Useful SQL Statements put here to copy to own page later

THIS DELETES ALL DATA WHERE DateCopied is more than 3 MONTHS ago
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;