SQL Command in VBA

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John W - 11 Jan 2008 15:13 GMT

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!

Reply to this Message

Armen Stein - 11 Jan 2008 15:29 GMT

>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

Reply to this Message

Stefan Hoffmann - 11 Jan 2008 15:33 GMT

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 <--

Reply to this Message

fredg - 11 Jan 2008 15:37 GMT

> 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
[quoted text clipped - 13 lines]
>
> 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.

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message

John W - 11 Jan 2008 20:41 GMT

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!

>> 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
[quoted text clipped - 20 lines]
> You cannot use RunSQL to run a Select query, only Action queries, i.e.
> Update, Insert, etc. or Pass-through queries.

Reply to this Message

fredg - 11 Jan 2008 21:05 GMT

> 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.
[quoted text clipped - 25 lines]
>> You cannot use RunSQL to run a Select query, only Action queries, i.e.
>> Update, Insert, etc. or Pass-through queries.

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

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message