howto Create recordset in VBA using SQL statement as string

[BlueWolverine]

Hello,

I have a query that doesn't work, though it should. I am really irritated
that I can't figure it out. There is a post from today under queries if you
are interested in addressing it.

Anyway, the reason I bring that up is I think I can work around that by
writing the select query in sql as a string, and then open that recordset in
vba. This way I can write the SQL and use a variable from VBA to narrow down
by my criteria, the problem is I have no idea how to do that.

I want to
1. generate a query from VBA

strSQL = cstr("SELECT t_FuelCardInventory.FuelCardProvider,
t_FuelCardInventory.VIN
FROM t_FuelCardInventory
WHERE (((t_FuelCardInventory.FuelCardProvider)='" & FCProvi & "') AND
((t_FuelCardInventory.VIN)='" & FCVIN & "'));")

2. perform the following action
Set RS = mydb.openrecordset(strSQL)

Alternatively,
Can you pass a VBA variable to an established and written query, so that in
design mode, the criteria looks like module!Program.FCVIN or soemthing like
that?

Here is the SQL that DOES NOT RETURN RECORDS I KNOW FOR FACT EXIST.. I can
scroll through the damn table and see them.
SELECT t_FuelCardInventory.FuelCardProvider, t_FuelCardInventory.VIN
FROM t_FuelCardInventory
WHERE (((t_FuelCardInventory.FuelCardProvider)='" &
[Forms]![f_SearchPanel]![GlobalFCProvi] & "') AND
((t_FuelCardInventory.VIN)='" & Forms![f_SearchPanel]![GlobalVIN] & "'));

I am using MS Access 2003 on XP Pro. Thank you

 

[Klatuu]

There is no join in the query that doesn't return records. They need to be
joined on a field common to both records.

Yes, you can modify the SQL of a store query. Here is an example:

Dim qdf As QueryDef
Dim strSQL As String

Set qdf = Currentdb.QuerDefs("NameOfQuery")
strSQL= qdf.SQL

'Make your changes here to the strSQL variable

qdf.SQL = strSQL
Set qdf = nothing

Now, one thing to be aware of when trying to make changes is that it will be
different next time you want to make another change. The way I do this is to
use two different queries. One I use as a template so it will always be the
same, and the other I use to save the modified version for actual use. In
this case, however you will using the query as a recordset within the same
procedure so you only need the one, but you don't change it. You just use it
as the template:

Dim qdfTemplate As QueryDef
Dim rst As Recordset
Dim strSQL As String

Set qdfTemplate = CurrentDb.QueryDefs("zzqselFuel")
strSQL = qdf.SQL
strSQl = Replace(strSQL,"ParmValue", Me.SomeControl)
Set rst = Currentdb.OpenRecordset(strSQL)

ParmValue is just something you put in the query string so you will know
where to make the change to the actual value
--
Dave Hargis, Microsoft Access MVP

Hello,

I have a query that doesn't work, though it should. I am really irritated
that I can't figure it out. There is a post from today under queries if you
are interested in addressing it.

Anyway, the reason I bring that up is I think I can work around that by
writing the select query in sql as a string, and then open that recordset in
vba. This way I can write the SQL and use a variable from VBA to narrow down
by my criteria, the problem is I have no idea how to do that.

I want to
1. generate a query from VBA

strSQL = cstr("SELECT t_FuelCardInventory.FuelCardProvider,
t_FuelCardInventory.VIN
FROM t_FuelCardInventory
WHERE (((t_FuelCardInventory.FuelCardProvider)='" & FCProvi & "') AND
((t_FuelCardInventory.VIN)='" & FCVIN & "'));")

2. perform the following action
Set RS = mydb.openrecordset(strSQL)

Alternatively,
Can you pass a VBA variable to an established and written query, so that in
design mode, the criteria looks like module!Program.FCVIN or soemthing like
that?

Here is the SQL that DOES NOT RETURN RECORDS I KNOW FOR FACT EXIST.. I can
scroll through the damn table and see them.
SELECT t_FuelCardInventory.FuelCardProvider, t_FuelCardInventory.VIN
FROM t_FuelCardInventory
WHERE (((t_FuelCardInventory.FuelCardProvider)='" &
[Forms]![f_SearchPanel]![GlobalFCProvi] & "') AND
((t_FuelCardInventory.VIN)='" & Forms![f_SearchPanel]![GlobalVIN] & "'));

I am using MS Access 2003 on XP Pro. Thank you

 

[BlueWolverine]

How do you join to fields on a FORM?

Those criteria in the SQL are just unlinked unbound fields on a FORM.

I have had queries look at fields in a form a 100 times but this one dies
for some reason.

Thank you for the work around though, I will try it out and see if it works.
--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!

There is no join in the query that doesn't return records. They need to be
joined on a field common to both records.

Yes, you can modify the SQL of a store query. Here is an example:

Dim qdf As QueryDef
Dim strSQL As String

Set qdf = Currentdb.QuerDefs("NameOfQuery")
strSQL= qdf.SQL

'Make your changes here to the strSQL variable

qdf.SQL = strSQL
Set qdf = nothing

Now, one thing to be aware of when trying to make changes is that it will be
different next time you want to make another change. The way I do this is to
use two different queries. One I use as a template so it will always be the
same, and the other I use to save the modified version for actual use. In
this case, however you will using the query as a recordset within the same
procedure so you only need the one, but you don't change it. You just use it
as the template:

Dim qdfTemplate As QueryDef
Dim rst As Recordset
Dim strSQL As String

Set qdfTemplate = CurrentDb.QueryDefs("zzqselFuel")
strSQL = qdf.SQL
strSQl = Replace(strSQL,"ParmValue", Me.SomeControl)
Set rst = Currentdb.OpenRecordset(strSQL)

ParmValue is just something you put in the query string so you will know
where to make the change to the actual value
--
Dave Hargis, Microsoft Access MVP

Hello,

I have a query that doesn't work, though it should. I am really irritated
that I can't figure it out. There is a post from today under queries if you
are interested in addressing it.

Anyway, the reason I bring that up is I think I can work around that by
writing the select query in sql as a string, and then open that recordset in
vba. This way I can write the SQL and use a variable from VBA to narrow down
by my criteria, the problem is I have no idea how to do that.

I want to
1. generate a query from VBA

strSQL = cstr("SELECT t_FuelCardInventory.FuelCardProvider,
t_FuelCardInventory.VIN
FROM t_FuelCardInventory
WHERE (((t_FuelCardInventory.FuelCardProvider)='" & FCProvi & "') AND
((t_FuelCardInventory.VIN)='" & FCVIN & "'));")

2. perform the following action
Set RS = mydb.openrecordset(strSQL)

Alternatively,
Can you pass a VBA variable to an established and written query, so that in
design mode, the criteria looks like module!Program.FCVIN or soemthing like
that?

Here is the SQL that DOES NOT RETURN RECORDS I KNOW FOR FACT EXIST.. I can
scroll through the damn table and see them.
SELECT t_FuelCardInventory.FuelCardProvider, t_FuelCardInventory.VIN
FROM t_FuelCardInventory
WHERE (((t_FuelCardInventory.FuelCardProvider)='" &
[Forms]![f_SearchPanel]![GlobalFCProvi] & "') AND
((t_FuelCardInventory.VIN)='" & Forms![f_SearchPanel]![GlobalVIN] & "'));

I am using MS Access 2003 on XP Pro. Thank you

 

[BlueWolverine]

That code does in fact work, but it takes a long time to run.
this line in particular takes about 3 seconds, compared to the rest, that's
forever.
<<<strSQL = qdf.SQL>>>
Is there anything I can do to speed that line up?


--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!

There is no join in the query that doesn't return records. They need to be
joined on a field common to both records.

Yes, you can modify the SQL of a store query. Here is an example:

Dim qdf As QueryDef
Dim strSQL As String

Set qdf = Currentdb.QuerDefs("NameOfQuery")
strSQL= qdf.SQL

'Make your changes here to the strSQL variable

qdf.SQL = strSQL
Set qdf = nothing

Now, one thing to be aware of when trying to make changes is that it will be
different next time you want to make another change. The way I do this is to
use two different queries. One I use as a template so it will always be the
same, and the other I use to save the modified version for actual use. In
this case, however you will using the query as a recordset within the same
procedure so you only need the one, but you don't change it. You just use it
as the template:

Dim qdfTemplate As QueryDef
Dim rst As Recordset
Dim strSQL As String

Set qdfTemplate = CurrentDb.QueryDefs("zzqselFuel")
strSQL = qdf.SQL
strSQl = Replace(strSQL,"ParmValue", Me.SomeControl)
Set rst = Currentdb.OpenRecordset(strSQL)

ParmValue is just something you put in the query string so you will know
where to make the change to the actual value
--
Dave Hargis, Microsoft Access MVP

Hello,

I have a query that doesn't work, though it should. I am really irritated
that I can't figure it out. There is a post from today under queries if you
are interested in addressing it.

Anyway, the reason I bring that up is I think I can work around that by
writing the select query in sql as a string, and then open that recordset in
vba. This way I can write the SQL and use a variable from VBA to narrow down
by my criteria, the problem is I have no idea how to do that.

I want to
1. generate a query from VBA

strSQL = cstr("SELECT t_FuelCardInventory.FuelCardProvider,
t_FuelCardInventory.VIN
FROM t_FuelCardInventory
WHERE (((t_FuelCardInventory.FuelCardProvider)='" & FCProvi & "') AND
((t_FuelCardInventory.VIN)='" & FCVIN & "'));")

2. perform the following action
Set RS = mydb.openrecordset(strSQL)

Alternatively,
Can you pass a VBA variable to an established and written query, so that in
design mode, the criteria looks like module!Program.FCVIN or soemthing like
that?

Here is the SQL that DOES NOT RETURN RECORDS I KNOW FOR FACT EXIST.. I can
scroll through the damn table and see them.
SELECT t_FuelCardInventory.FuelCardProvider, t_FuelCardInventory.VIN
FROM t_FuelCardInventory
WHERE (((t_FuelCardInventory.FuelCardProvider)='" &
[Forms]![f_SearchPanel]![GlobalFCProvi] & "') AND
((t_FuelCardInventory.VIN)='" & Forms![f_SearchPanel]![GlobalVIN] & "'));

I am using MS Access 2003 on XP Pro. Thank you

 

[Douglas J. Steele]

Are you actually modifying strSQL, or are you just building strSQL from
scratch before resetting qdf.SQL?

If you're building the SQL from scratch, just eliminate that line.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

That code does in fact work, but it takes a long time to run.
this line in particular takes about 3 seconds, compared to the rest,
that's
forever.
<<<strSQL = qdf.SQL>>>
Is there anything I can do to speed that line up?


--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!

There is no join in the query that doesn't return records. They need to
be
joined on a field common to both records.

Yes, you can modify the SQL of a store query. Here is an example:

Dim qdf As QueryDef
Dim strSQL As String

Set qdf = Currentdb.QuerDefs("NameOfQuery")
strSQL= qdf.SQL

'Make your changes here to the strSQL variable

qdf.SQL = strSQL
Set qdf = nothing

Now, one thing to be aware of when trying to make changes is that it will
be
different next time you want to make another change. The way I do this
is to
use two different queries. One I use as a template so it will always be
the
same, and the other I use to save the modified version for actual use. In
this case, however you will using the query as a recordset within the
same
procedure so you only need the one, but you don't change it. You just
use it
as the template:

Dim qdfTemplate As QueryDef
Dim rst As Recordset
Dim strSQL As String

Set qdfTemplate = CurrentDb.QueryDefs("zzqselFuel")
strSQL = qdf.SQL
strSQl = Replace(strSQL,"ParmValue", Me.SomeControl)
Set rst = Currentdb.OpenRecordset(strSQL)

ParmValue is just something you put in the query string so you will know
where to make the change to the actual value
--
Dave Hargis, Microsoft Access MVP

Hello,

I have a query that doesn't work, though it should. I am really
irritated
that I can't figure it out. There is a post from today under queries
if you
are interested in addressing it.

Anyway, the reason I bring that up is I think I can work around that by
writing the select query in sql as a string, and then open that
recordset in
vba. This way I can write the SQL and use a variable from VBA to
narrow down
by my criteria, the problem is I have no idea how to do that.

I want to
1. generate a query from VBA

strSQL = cstr("SELECT t_FuelCardInventory.FuelCardProvider,
t_FuelCardInventory.VIN
FROM t_FuelCardInventory
WHERE (((t_FuelCardInventory.FuelCardProvider)='" & FCProvi & "') AND
((t_FuelCardInventory.VIN)='" & FCVIN & "'));")

2. perform the following action
Set RS = mydb.openrecordset(strSQL)

Alternatively,
Can you pass a VBA variable to an established and written query, so
that in
design mode, the criteria looks like module!Program.FCVIN or soemthing
like
that?

Here is the SQL that DOES NOT RETURN RECORDS I KNOW FOR FACT EXIST.. I
can
scroll through the damn table and see them.
SELECT t_FuelCardInventory.FuelCardProvider, t_FuelCardInventory.VIN
FROM t_FuelCardInventory
WHERE (((t_FuelCardInventory.FuelCardProvider)='" &
[Forms]![f_SearchPanel]![GlobalFCProvi] & "') AND
((t_FuelCardInventory.VIN)='" & Forms![f_SearchPanel]![GlobalVIN] &
"'));

I am using MS Access 2003 on XP Pro. Thank you

 

[BlueWolverine]

I retract my previous statement the code ran fast enough.
--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!

That code does in fact work, but it takes a long time to run.
this line in particular takes about 3 seconds, compared to the rest, that's
forever.
<<<strSQL = qdf.SQL>>>
Is there anything I can do to speed that line up?


--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!

There is no join in the query that doesn't return records. They need to be
joined on a field common to both records.

Yes, you can modify the SQL of a store query. Here is an example:

Dim qdf As QueryDef
Dim strSQL As String

Set qdf = Currentdb.QuerDefs("NameOfQuery")
strSQL= qdf.SQL

'Make your changes here to the strSQL variable

qdf.SQL = strSQL
Set qdf = nothing

Now, one thing to be aware of when trying to make changes is that it will be
different next time you want to make another change. The way I do this is to
use two different queries. One I use as a template so it will always be the
same, and the other I use to save the modified version for actual use. In
this case, however you will using the query as a recordset within the same
procedure so you only need the one, but you don't change it. You just use it
as the template:

Dim qdfTemplate As QueryDef
Dim rst As Recordset
Dim strSQL As String

Set qdfTemplate = CurrentDb.QueryDefs("zzqselFuel")
strSQL = qdf.SQL
strSQl = Replace(strSQL,"ParmValue", Me.SomeControl)
Set rst = Currentdb.OpenRecordset(strSQL)

ParmValue is just something you put in the query string so you will know
where to make the change to the actual value
--
Dave Hargis, Microsoft Access MVP

Hello,

I have a query that doesn't work, though it should. I am really irritated
that I can't figure it out. There is a post from today under queries if you
are interested in addressing it.

Anyway, the reason I bring that up is I think I can work around that by
writing the select query in sql as a string, and then open that recordset in
vba. This way I can write the SQL and use a variable from VBA to narrow down
by my criteria, the problem is I have no idea how to do that.

I want to
1. generate a query from VBA

strSQL = cstr("SELECT t_FuelCardInventory.FuelCardProvider,
t_FuelCardInventory.VIN
FROM t_FuelCardInventory
WHERE (((t_FuelCardInventory.FuelCardProvider)='" & FCProvi & "') AND
((t_FuelCardInventory.VIN)='" & FCVIN & "'));")

2. perform the following action
Set RS = mydb.openrecordset(strSQL)

Alternatively,
Can you pass a VBA variable to an established and written query, so that in
design mode, the criteria looks like module!Program.FCVIN or soemthing like
that?

Here is the SQL that DOES NOT RETURN RECORDS I KNOW FOR FACT EXIST.. I can
scroll through the damn table and see them.
SELECT t_FuelCardInventory.FuelCardProvider, t_FuelCardInventory.VIN
FROM t_FuelCardInventory
WHERE (((t_FuelCardInventory.FuelCardProvider)='" &
[Forms]![f_SearchPanel]![GlobalFCProvi] & "') AND
((t_FuelCardInventory.VIN)='" & Forms![f_SearchPanel]![GlobalVIN] & "'));

I am using MS Access 2003 on XP Pro. Thank you