Access Query -> ADO query using DSN

[aogail]

Hello,

I have an Access database which links to tables on an MS SQL Server. It has a query which pulls together information from three different tables. Two of the tables are in one database; the third is in another. The query is supposed to grab the first full set of information it finds for a particular production unit (reel). The query is as follows, and works as expected from Access:

SELECT TOP 1 [dbo_reel].[reel_key], [dbo_Roll_Summary].[cust_key_end_user], [dbo_reel].[dt_turnup], [dbo_reel].[avg_speed_fpm], [dbo_reel].[birth_bwt_act], [dbo_reel].[birth_moisture], [dbo_Quality_Data].[Opacity_(6S20)], [dbo_Quality_Data].[TEAR_md_(2M80)], [dbo_Quality_Data].[TEAR_cd_(2C80)], [dbo_Quality_Data].[TENS_cd_(2C10)], [dbo_Quality_Data].[BRT_t_(6T10)], [dbo_Quality_Data].[SMOOTH_t_(4T40)], [dbo_Quality_Data].[SMOOTH_b_(4B40)], [dbo_Quality_Data].[POROSITY_(5S30)], [dbo_Quality_Data].[TEA_md_(2M60)]

FROM dbo_Quality_Data INNER JOIN (dbo_reel INNER JOIN dbo_Roll_Summary ON [dbo_reel].[reel_key]=[dbo_Roll_Summary].[roll_reel]) ON [dbo_Quality_Data].[sample_key]=[dbo_reel].[reel_key]

WHERE ((([dbo_reel].[reel_key])=[Reel Key]) And (([dbo_Quality_Data].[Opacity_(6S20)]) Is Not Null));

Now, I need to convert this query to a form I can use with ADO. I created a DSN for the SQL Server and pass its name to the ADODB.Connection object. I realized that the table names in Access are not those on SQL Server. Through the SQL Server database list and small test SELECT queries I found the database & table names I need and that ADO (or SQL Server?) does not like square brackets in the statement. My "converted" query is:

SELECT TOP 1 rep_snnb.dbo.reel.reel_key, Technical.dbo.Roll_Summary.cust_key_end_user, rep_snnb.dbo.reel.dt_turnup, rep_snnb.dbo.reel.avg_speed_fpm, rep_snnb.dbo.reel.birth_bwt_act, rep_snnb.dbo.reel.birth_moisture, Technical.dbo.Quality_Data.Opacity_(6S20), Technical.dbo.Quality_Data.TEAR_md_(2M80), Technical.dbo.Quality_Data.TEAR_cd_(2C80), Technical.dbo.Quality_Data.TENS_cd_(2C10), Technical.dbo.Quality_Data.BRT_t_(6T10), Technical.dbo.Quality_Data.SMOOTH_t_(4T40), Technical.dbo.Quality_Data.SMOOTH_b_(4B40), Technical.dbo.Quality_Data.POROSITY_(5S30) , Technical.dbo.Quality_Data.TEA_md_(2M60) 

FROM Technical.dbo.Quality_Data INNER JOIN (rep_snnb.dbo.reel INNER JOIN Technical.dbo.Roll_Summary ON rep_snnb.dbo.reel.reel_key=Technical.dbo.Roll_Summary.roll_reel) ON Technical.dbo.Quality_Data.sample_key = rep_snnb.dbo.reel.reel_key 

WHERE (((rep_snnb.dbo.reel.reel_key)='" & reelKey & "') And ((Technical.dbo.Quality_Data.Opacity_(6S20)) Is Not Null));

So, [dbo_reel] = rep_snnb.dbo.reel; [dbo_Roll_Summary] = Technical.dbo.Roll_Summary; and [dbo_Quality_Data] = Technical.dbo.Quality_Data.

Unfortunately, the converted query doesn't work. When I run the following code, I get a "Run-time Automation" error at the last line:

   Dim rs As ADODB.recordSet
   Dim conn As ADODB.connection
   Dim sql As String

   Set rs = New ADODB.recordSet
   Set conn = New ADODB.connection
' SPNEWBERG14 = ODBC data source name
   conn.Open "SPNEWBERG14"
   sql = (query)
   rs.Open sql, conn, adOpenDynamic, adLockOptimistic

I've also tried simple JOINing queries such as this one, but still get the error.

SELECT TOP 1 rep_snnb.dbo.reel.reel_key, Technical.dbo.Roll_Summary.cust_key_end_user FROM rep_snnb.reel INNER JOIN Technical.Roll_Summary ON rep_snnb.dbo.reel.reel_key = Technical.dbo.Roll_Summary.roll_reel WHERE rep_snnb.dbo.reel.reel_key = '5H0101';

Only the most simple SELECT queries work:

SELECT rep_snnb.dbo.reel.reel_key FROM rep_snnb.dbo.reel WHERE rep_snnb.dbo.reel.reel_key='5H0101';

Perhaps it has something to do with my JOIN statements, but they were essentially (except for the name conversion and removal of square brackets) created by Access and should work--or am I wrong in assuming that?

Thank you for any light you can shed.

- Ben

 

[BobRodes]

Hi.

It looks like you wrote the original query using the Query by Example screen (dragging tables, check marking included fields, etc.)

If you're comfortable using that arrangement, perhaps you would find the data environment the easiest path to your goal. I wouldn't recommend an ODBC link with ADO, unless you have special reason to do it. Use OLE DB instead.

With the data environment, just set the properties of Connection1 (change its name, too). Then, right click on the connection and add a command. Use the SQL Builder to build your query as you did in Access.

If you prefer from there, go into Debug mode and evaluate properties of the data environment, such as de.conn.Connectionstring and so on. You can then build an ADO routine similar to the one on your screen, and cut and paste the values that you get from your debug evaluations.

While the data environment might represent more than necessary code overhead, it's a good learning tool as well.

Good luck!

Bob Rodes

 

[aogail]

Hi Bob,

First, thanks for your reply.

This morning I tried different queries and shifting things around and noticed it wasn't the JOIN statement that kept the query from running, but the fields with parentheses. I tried putting square brackets around only the field name--and now it works.

Thank you again for your post, though. It did help me find another typo, but my thought process that took me from "Try the Data Environment" to the solution is so convoluted I'm not sure how to explain it. ;-)