SQL Command in VBA

[John W]

I'm attempting to write a SQL statement in VBA to select specific records
from a table but I'm not having much luck. What I need to do is select all
records from Table 2 where Field 1 is equal to Field 1 in Table 1. I've
defined a variable ("cl_ID") that I can set equal to Field 1 in Table 1. I
guess I just need a basic example of what to do. I've looked online but all
the examples are for more complicated situations.

I'm trying something like

dim mySQL as string

mySQL = "SELECT * FROM Table2"
"WHERE Field1 = cl_ID"

docmd.runSQL mySQL

Thanks for the help...I know this is a pretty easy question!

 

[Armen Stein]

mySQL = "SELECT * FROM Table2"
"WHERE Field1 = cl_ID"

Hi John,

The SQL statement doesn't know about your VBA variable. You need to
evaluate it before sending it to SQL and concatenate it together.
Something like:

mySQL = "SELECT * FROM Table2 WHERE Field1 = " & cl_ID

This assumes that cl_ID is numeric - you would need to concatenate
some quotes around it if it's alpha.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Stefan Hoffmann]

hi John,

I'm attempting to write a SQL statement in VBA to select specific records
from a table but I'm not having much luck. What I need to do is select all
records from Table 2 where Field 1 is equal to Field 1 in Table 1.

Sounds like a join.

dim mySQL as string

mySQL = "SELECT * FROM Table2"
"WHERE Field1 = cl_ID"

docmd.runSQL mySQL

RunSQL can only execute "action" SQL, thus means INSERT, UPDATE, DELETE,
but not SELECT.

When you have a bound form to Table2, just use

Me.Filter = "Field1 = " & cl_ID
Me.FilterOn = True

or replace the record source:

Me.RecordSource = "SELECT * FROM Table2 WHERE Field1 = " & cl_ID


mfG
--> stefan <--

 

[fredg]

I'm attempting to write a SQL statement in VBA to select specific records
from a table but I'm not having much luck. What I need to do is select all
records from Table 2 where Field 1 is equal to Field 1 in Table 1. I've
defined a variable ("cl_ID") that I can set equal to Field 1 in Table 1. I
guess I just need a basic example of what to do. I've looked online but all
the examples are for more complicated situations.

I'm trying something like

dim mySQL as string

mySQL = "SELECT * FROM Table2"
"WHERE Field1 = cl_ID"

docmd.runSQL mySQL

Thanks for the help...I know this is a pretty easy question!

See VBA Help on RunSQL.
You cannot use RunSQL to run a Select query, only Action queries, i.e.
Update, Insert, etc. or Pass-through queries.

 

[John W]

Thanks for all the info! I was starting to wonder if a simple select query
could not be done in VBA. I'll give the me.Filter option a try.

Thanks again!

 

[fredg]

Thanks for all the info! I was starting to wonder if a simple select query
could not be done in VBA. I'll give the me.Filter option a try.

Thanks again!

You can create a Select query using VBA, you just can't use RunSQL.

Public Sub MakeAQuery()
Dim qdf As DAO.QueryDef
Dim Db As DAO.Database
Set Db = CurrentDb
Dim strSQL As String
strSQL = "Select YourTable.LastName from YourTable Order by
[LastName];"
Set qdf = Db.CreateQueryDef("MyQdf", strSQL)

DoCmd.OpenQuery "myQdf"

End Sub