CODEWITHSUNDEEP
ms-accessvbasyntax-errorruntime-error
MusicMan
MusicMan

Reputation: 37

VBA Run-time error 3075

I'm trying to write an SQL query in VBA but it's not working. Not sure why, its a simple SELECT query. Below is my SQL query:

strSQLPharmContact = "SELECT TOP 1 tbl_Contacts.ID, 
tbl_Contacts.idSite, tbl_Contacts.role, tbl_Contacts.name, 
tbl_Contacts.email, tbl_Contacts.phone, tbl_Contacts.involvement, 
tbl_Contacts.Taken" _ & "FROM tbl_Contacts " _
& "WHERE (((tbl_Contacts.role)= 'Pharmacist') AND 
((tbl_Contacts.involvement)=True) AND ((tbl_Contacts.Taken)=False)); "

Cheers guys

Upvotes: 0

Views: 38

Answers (2)

Dan Donoghue
Dan Donoghue

Reputation: 6216

Why so many brackets?

When using VBA to hit a DB, I always like to structure my SQL query so it looks exactly like I would enter it into an SQL Query tool.

Run and have a look in the debug window (ctrl-g)

Much neater and as a result much easier to modify and trouble shoot:

Sub sql()
    strSQLPharmContact = "SELECT TOP 1 tbl_Contacts.ID,"
    strSQLPharmContact = strSQLPharmContact & vbLf & "       tbl_Contacts.idSite,"
    strSQLPharmContact = strSQLPharmContact & vbLf & "       tbl_Contacts.role,"
    strSQLPharmContact = strSQLPharmContact & vbLf & "       tbl_Contacts.name,"
    strSQLPharmContact = strSQLPharmContact & vbLf & "       tbl_Contacts.email,"
    strSQLPharmContact = strSQLPharmContact & vbLf & "       tbl_Contacts.phone,"
    strSQLPharmContact = strSQLPharmContact & vbLf & "       tbl_Contacts.involvement,"
    strSQLPharmContact = strSQLPharmContact & vbLf & "       tbl_Contacts.Taken"
    strSQLPharmContact = strSQLPharmContact & vbLf & "FROM   tbl_Contacts "
    strSQLPharmContact = strSQLPharmContact & vbLf & "WHERE  tbl_Contacts.role= 'Pharmacist'"
    strSQLPharmContact = strSQLPharmContact & vbLf & "AND    tbl_Contacts.involvement = True"
    strSQLPharmContact = strSQLPharmContact & vbLf & "AND    tbl_Contacts.Taken = False"
    Debug.Print strSQLPharmContact
End Sub

Upvotes: 0

Rahul
Rahul

Reputation: 77926

Most probably the error is because of spacing issue in your query. You need a space as shown below

& " FROM tbl_Contacts " _
   ^---- Here

Otherwise, your query string looks like

SELECT TOP 1 tbl_Contacts.ID, tbl_Contacts.idSite, 
tbl_Contacts.role, tbl_Contacts.name, 
tbl_Contacts.email, tbl_Contacts.phone, 
tbl_Contacts.involvement, tbl_Contacts.TakenFROM tbl_Contacts 
                                            ^-- ERROR Here

Upvotes: 2

Related Questions