How to Use a SQL Select Statement with Access Vba

How to use a SQL SELECT statement with Access VBA

If you wish to use the bound column value, you can simply refer to the combo:

sSQL = "SELECT * FROM MyTable WHERE ID = " & Me.MyCombo

You can also refer to the column property:

sSQL = "SELECT * FROM MyTable WHERE AText = '" & Me.MyCombo.Column(1) & "'"

Dim rs As DAO.Recordset     
Set rs = CurrentDB.OpenRecordset(sSQL)

strText = rs!AText
strText = rs.Fields(1)

In a textbox:

= DlookUp("AText","MyTable","ID=" & MyCombo)

*edited

Access VBA simple sql select statement

You are missing = in the condition

try below

matterSQL = "SELECT type FROM tblMatter WHERE id='" & id & "'"

Also if id is numeric you don't need '

matterSQL = "SELECT type FROM tblMatter WHERE id=" & id 

Too few parameters. Expected 1.
This happens when the field name in your sql query do not match the table field name
if the field name are correct i believe the the datatype of category is not numeric then you have to use '

costSQL = "SELECT email FROM tblScaleOfDisb WHERE category='" & category &"'"

Always try to use parameterised query to avoid SQL injection

Microsoft Access VBA code with Select SQL String and Where clause

Try,

    Dim strSQL As String
    strSQL = "SELECT StakeholderRegister.[StakeholderID], StakeholderRegister.[ProjectID], StakeholderRegister.[FirstName], StakeholderRegister.[LastName], StakeholderRegister.[EmailAddress] " & _
    " FROM StakeholderRegister " & _
    " WHERE StakeholderRegister.[ProjectID]=" & [Forms]![ChangeLog]![cboProjectID].Value & " ;"
Set rs = Db.OpenRecordset(strSQL)

if [ProjectID] field type is text then

    Dim strSQL As String
    strSQL = "SELECT StakeholderRegister.[StakeholderID], StakeholderRegister.[ProjectID], StakeholderRegister.[FirstName], StakeholderRegister.[LastName], StakeholderRegister.[EmailAddress] " & _
    " FROM StakeholderRegister " & _
    " WHERE StakeholderRegister.[ProjectID]='" & [Forms]![ChangeLog]![cboProjectID].Value & "' ;"
Set rs = Db.OpenRecordset(strSQL)

How do I get the result of a Select SQL statement in VBA

To print value of field from only first record.

Dim strSQL As String
Dim result As DAO.Recordset
strSQL = "SELECT x FROM y WHERE z;"
Set result = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
Debug.Print result!x

If you want to print a value from each record, use a loop structure to move to each record like:

Do While Not result.EOF
    Debug.Print result!x
    result.MoveNext
Loop

Review this site http://allenbrowne.com/tips.html - look at the section 'Examples by Library' about halfway down.

In Excel VBA run SQL SELECT ... INTO ... IN .... Statement

You cannot assign an action query like a make-table query (i.e., SELECT with INTO call) to a recordset. Consider executing your DROP and SELECT ... INTO action queries prior to opening recordset on the local table. Also, it is unclear why you are opening a second database or what the path points to. Below opens a recordset on the mainframe data:

Set DB = OpenDatabase(LocalDBPath, False, False)

DB.Execute "DROP TABLE DB2_TABLEA", dbFailOnError
DB.Execute sSQL, dbFailOnError

Set RS = DB.OpenRecordset("SELECT * FROM DB2_TABLEA")

---

Furthermore, the IN clause in your make-table query is unnecessary as you are currently connected to the very database you are running the action on. Simply remove it ('" & LocalDBPath & "'). Also, LIKE expressions without wildcards and on numbers should be replaced with =

SELECT 
    DB1_TABLEA.FIELD1, 
    DB1_TABLEA.FIELD2, 
    DB1_TABLEA.FIELD3, 
    DB1_TABLEA.FIELD4, 
    DB1_TABLEA.FIELD5, 
    DB1_TABLEA.FIELD6, 
    DB1_TABLEA.FIELD7, 
    DB1_TABLEA.FIELD8 
INTO 
    DB2_TABLEA 
FROM 
    DB1_TABLEA
WHERE 
    (
        ((DB1_TABLEA.FIELD4) = 99999) 
        AND 
        ((DB1_TABLEA.FIELD6)='02' OR (DB1_TABLEA.FIELD6)='22')
    )
;

In fact, consider saving the query inside the MS Access database (Ribbon -> Create -> Query Design -> SQL View) and call it as a named object and avoid any long SQL in VBA.

DB.Execute "DROP TABLE DB2_TABLEA", dbFailOnError
DB.Execute "mySavedQuery", dbFailOnError

Set RS = DB.OpenRecordset("SELECT * FROM DB2_TABLEA")

Excel VBA Use ADO Command Object to Call Stored Query in Access

adCmdStoredProc doesn't seem to be designed for MsAccess queries. Change it to adCmdTable. Also, when setting the parameter values they are indexed from zero.

 Dim conn As ADODB.Connection
 Dim rs As ADODB.Recordset
 Dim cmd As New ADODB.Command

 Set conn = makeConnection()
 cmd.CommandText = "accessLevelByUN"
 cmd.CommandType = adCmdTable

 cmd.ActiveConnection = conn

 cmd.Parameters.Refresh

 MsgBox (cmd.Parameters.Count)

 cmd.Parameters(0) = userName

 Set rs = cmd.Execute
...

Using VBA Function in Access Query

You need to feed a single column to your function.

Your query is rather confusing, I think it is as simple as this:

SELECT 
  getfirstletters(sl.locationname),
  sbi.samplebasicinformationid 
FROM samplebasicinformation as sbi 
  INNER JOIN samplelocation as sl 
  on sbi.samplelocationid = sl.samplelocationid

Note that you need INNER JOIN in Access Sql.

---

Related Topics