SQL query to Access database

  • Thread starter Thread starter CharlesEF
  • Start date Start date
C

CharlesEF

Hi All,

I use ADO to query my Access database. I have no problems with that.
What I would like to ask is new to me so I hope I can be clear.

I query TableA for model numbers. The recordset returns the results I
expect, no problem. My next step is to query TableB, to get the model
description, using the returned recordset of TableA. Can I do this
using 1 query? Maybe using a JOIN or something?

Currently I open TableB, loop through the TableA recordset and use the
Find command to return the model description.

While this works fine I am wondering if there is a better way.


Thanks for any help,

Charles
 
Hi All,

I use ADO to query my Access database. I have no problems with that.
What I would like to ask is new to me so I hope I can be clear.

I query TableA for model numbers. The recordset returns the results I
expect, no problem. My next step is to query TableB, to get the model
description, using the returned recordset of TableA. Can I do this
using 1 query? Maybe using a JOIN or something?

Currently I open TableB, loop through the TableA recordset and use the
Find command to return the model description.

While this works fine I am wondering if there is a better way.


Thanks for any help,

Charles
Click to expand...

You can certainly use a multitable query to retrieve your data, with no need
for two separate queries or any recordset operations:

SELECT <whatever fields you want>
FROM TableA INNER JOIN TableB
ON TableA.ModelID = TableB.ModelID

using your actual tablenames and join fields.
 
You can certainly use a multitable query to retrieve your data, with no need
for two separate queries or any recordset operations:

SELECT <whatever fields you want>
FROM TableA INNER JOIN TableB
ON TableA.ModelID = TableB.ModelID

using your actual tablenames and join fields.
Click to expand...

Hi,

Thanks for your idea, seems simple once I think about it. However; as
simple as it seems I can't get it to work correctly. I get no error
message but the data returned is not what it should be. This is run
using javascript in an HTA application.

This is my original SELECT statement - all 1 long line.
objMatrixRS.Open("SELECT DISTINCT [OperatingSystemID] FROM Matrix
WHERE [ModelID] = " + id + " AND [AppliesToOS] = " + true + " ORDER BY
[OperatingSystemID] ASC", objFileCon, adOpenDynamic,
adLockOptimistic);

This is the new SELECT statement - all 1 long line.
objOSRS.Open("SELECT DISTINCT [OperatingSystems.ID],
[OperatingSystems.OperatingSystem] FROM Matrix INNER JOIN
OperatingSystems ON Matrix.ModelID = OperatingSystems.ID WHERE
Matrix.ModelID = " + id + " AND Matrix.AppliesToOS = " + true + "
ORDER BY OperatingSystems.ID ASC", objFileCon, adOpenDynamic,
adLockOptimistic);


Can you see my mistake? I can't.


Thanks for any more help,

Charles
 
This is the new SELECT statement - all 1 long line.
objOSRS.Open("SELECT DISTINCT [OperatingSystems.ID],
[OperatingSystems.OperatingSystem] FROM Matrix INNER JOIN
OperatingSystems ON Matrix.ModelID = OperatingSystems.ID WHERE
Matrix.ModelID = " + id + " AND Matrix.AppliesToOS = " + true + "
ORDER BY OperatingSystems.ID ASC", objFileCon, adOpenDynamic,
adLockOptimistic);
Click to expand...

The bracketing is wrong: the tablenames should be in brackets, and the
fieldnames should be in brackets - but not the combination. Try

objOSRS.Open("SELECT DISTINCT [OperatingSystems].[ID],
[OperatingSystems].[OperatingSystem] FROM Matrix INNER JOIN
OperatingSystems ON Matrix.ModelID = OperatingSystems.ID WHERE
Matrix.ModelID = " + id + " AND Matrix.AppliesToOS = " + true + "
ORDER BY OperatingSystems.ID ASC", objFileCon, adOpenDynamic,
adLockOptimistic);

Or, since your names don't contain blanks or special characters, just leave
out the [] altogether.

This does assume that ModelID is numeric, AppliesToOS is a Boolean (Yes/No)
field, and that the constant True is equal to -1. You could just use the word
true as a criterion since Access will recognize it correctly:

" AND Matrix.AppliesToOS = True"
 
This is the new SELECT statement - all 1 long line.
objOSRS.Open("SELECT DISTINCT [OperatingSystems.ID],
[OperatingSystems.OperatingSystem] FROM Matrix INNER JOIN
OperatingSystems ON Matrix.ModelID = OperatingSystems.ID WHERE
Matrix.ModelID = " + id + " AND Matrix.AppliesToOS = " + true + "
ORDER BY OperatingSystems.ID ASC", objFileCon, adOpenDynamic,
adLockOptimistic);
Click to expand...

The bracketing is wrong: the tablenames should be in brackets, and the
fieldnames should be in brackets - but not the combination. Try

objOSRS.Open("SELECT DISTINCT [OperatingSystems].[ID],
[OperatingSystems].[OperatingSystem] FROM Matrix INNER JOIN
OperatingSystems ON Matrix.ModelID = OperatingSystems.ID WHERE
Matrix.ModelID = " + id + " AND Matrix.AppliesToOS = " + true + "
ORDER BY OperatingSystems.ID ASC", objFileCon, adOpenDynamic,
adLockOptimistic);

Or, since your names don't contain blanks or special characters, just leave
out the [] altogether.

This does assume that ModelID is numeric, AppliesToOS is a Boolean (Yes/No)
field, and that the constant True is equal to -1. You could just use the word
true as a criterion since Access will recognize it correctly:

" AND Matrix.AppliesToOS = True"
Click to expand...

Hi,

Seems like ADO or something requires the brackets. If I use your
SELECT statement then I get the error: 'Item cannot be found in the
collection corresponding to the requested name or ordinal'. If I
remove the brackets then I get the same error message. Seems the
bracket format required is [Table.Field] in my case. You are correct
when you state: 'This assumes that ModelID is numeric, AppliesToOS is
a Boolean (Yes/No) field'.

This is my current SELECT statement - all 1 long line.
objOSRS.Open("SELECT DISTINCT [OperatingSystems.ID],
[OperatingSystems.OperatingSystem] FROM Matrix INNER JOIN
OperatingSystems ON Matrix.ModelID = OperatingSystems.ID WHERE
Matrix.ModelID = " + id + " AND Matrix.AppliesToOS = " + true + "
ORDER BY OperatingSystems.ID ASC", objFileCon, adOpenDynamic,
adLockOptimistic);

It returns the wrong results. In fact, I figured out what data is
returned but not why.

I have a <SELECT> dropdown box in my HTA listing all the models. The
value of each listing is the ModelID. I use parseInt to convert it to
numeric and pass it to the on_change function. I know the correct
ModelID is passed to the function, I can see it with an alert command.
I have 18 OS's listed in the OperatingSystems table, with IDs 1-18.
When I select one of the first 18 models listed then I get that value
returned by the SELECT statement. Once I select any model after 18
then I get 0 records returned.

So, if I select listing 1 then the SELECT statement returns 1. If I
select listing 10 then 10 is returned. If I select listing 18 then 18
is returned. These results are not correct. I should get a recordset
returned that show all OS's supported by that ModelID.

As I stated before, my original SELECT statement did return the
correct results. I am really lost now.


Thanks for your time and help,

Charles
 
This is the new SELECT statement - all 1 long line.
objOSRS.Open("SELECT DISTINCT [OperatingSystems.ID],
[OperatingSystems.OperatingSystem] FROM Matrix INNER JOIN
OperatingSystems ON Matrix.ModelID = OperatingSystems.ID WHERE
Matrix.ModelID = " + id + " AND Matrix.AppliesToOS = " + true + "
ORDER BY OperatingSystems.ID ASC", objFileCon, adOpenDynamic,
adLockOptimistic);
Click to expand...
Click to expand...
The bracketing is wrong: the tablenames should be in brackets, and the
fieldnames should be in brackets - but not the combination. Try
Click to expand...
objOSRS.Open("SELECT DISTINCT [OperatingSystems].[ID],
[OperatingSystems].[OperatingSystem] FROM Matrix INNER JOIN
OperatingSystems ON Matrix.ModelID = OperatingSystems.ID WHERE
Matrix.ModelID = " + id + " AND Matrix.AppliesToOS = " + true + "
ORDER BY OperatingSystems.ID ASC", objFileCon, adOpenDynamic,
adLockOptimistic);
Click to expand...
Or, since your names don't contain blanks or special characters, just leave
out the [] altogether.
Click to expand...
This does assume that ModelID is numeric, AppliesToOS is a Boolean (Yes/No)
field, and that the constant True is equal to -1. You could just use the word
true as a criterion since Access will recognize it correctly:
Click to expand...
" AND Matrix.AppliesToOS = True"
--
Click to expand...
John W. Vinson [MVP]
Click to expand...

Hi,

Seems like ADO or something requires the brackets. If I use your
SELECT statement then I get the error: 'Item cannot be found in the
collection corresponding to the requested name or ordinal'. If I
remove the brackets then I get the same error message. Seems the
bracket format required is [Table.Field] in my case. You are correct
when you state: 'This assumes that ModelID is numeric, AppliesToOS is
a Boolean (Yes/No) field'.

This is my current SELECT statement - all 1 long line.
objOSRS.Open("SELECT DISTINCT [OperatingSystems.ID],
[OperatingSystems.OperatingSystem] FROM Matrix INNER JOIN
OperatingSystems ON Matrix.ModelID = OperatingSystems.ID WHERE
Matrix.ModelID = " + id + " AND Matrix.AppliesToOS = " + true + "
ORDER BY OperatingSystems.ID ASC", objFileCon, adOpenDynamic,
adLockOptimistic);

It returns the wrong results. In fact, I figured out what data is
returned but not why.

I have a <SELECT> dropdown box in my HTA listing all the models. The
value of each listing is the ModelID. I use parseInt to convert it to
numeric and pass it to the on_change function. I know the correct
ModelID is passed to the function, I can see it with an alert command.
I have 18 OS's listed in the OperatingSystems table, with IDs 1-18.
When I select one of the first 18 models listed then I get that value
returned by the SELECT statement. Once I select any model after 18
then I get 0 records returned.

So, if I select listing 1 then the SELECT statement returns 1. If I
select listing 10 then 10 is returned. If I select listing 18 then 18
is returned. These results are not correct. I should get a recordset
returned that show all OS's supported by that ModelID.

As I stated before, my original SELECT statement did return the
correct results. I am really lost now.

Thanks for your time and help,

Charles
Click to expand...

Hi,

I came across some VBScript code that uses MSDataShape provider and I
was able to convert it to javascript. Below is the working code:

This code is in the global section of the HTA:
objFileCon = new ActiveXObject("ADODB.Connection");
objFileCon.Open("Provider=MSDataShape;Data
Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=database\
\SonyInfo.mdb");

This is the OnChange function:
function OnChange_Model(id)
{
objMatrixRS = new ActiveXObject("ADODB.Recordset");
objMatrixRS.Open("SHAPE {SELECT DISTINCT [OperatingSystemID] FROM
Matrix WHERE [Matrix].[ModelID] = " + id + " AND [Matrix].
[AppliesToOS] = " + true + " ORDER BY [Matrix].[OperatingSystemID]
Asc} APPEND ({SELECT [ID], [Description] FROM OperatingSystems} AS
osRS RELATE [OperatingSystemID] TO [ID])", objFileCon, adOpenDynamic,
adLockOptimistic);
do
{
objOSRS = new Object(objMatrixRS.Fields("osRS").Value);
window.alert("OS: " + objOSRS.Fields("ID").Value + " - " +
objOSRS.Fields("Description").Value + "\n");
objMatrixRS.MoveNext;
}
while(!objMatrixRS.EOF);
objOSRS.Close;
objMatrixRS.Close;
objOSRS = null;
objMatrixRS = null;
}


If you will notice I got your suggestion about brackets to work,
.[Field]. When I have some time tomorrow I will test your
suggestion again. I must have been doing something wrong. I will
post back with the results of the test.


Thanks for all your help,

Charles
 
Back
Top