pass-thru query to MS Access -- connex difficulty 1

[GKChesterton]

MAS 90 level 4.05 (Release Nov. 2004)
Access 2003.

I want to read data from MAS 90 using Access. A good start would be the test query
SELECT SalesOrderNumber, CustomerNumber
FROM SO1_SOEntryHeader
WHERE CustomerNumber="0214005";.

I can run queries through MAS 90 using ODBC linked tables, but they're slow and tend to time out (although I do increase the time-out property). I want to run them more quickly and also not break for entering User and Password.

Some threads advise a pass-through query using a silent DSN. To do this, I set the query as "SQL-specific pass through" and fill in the ODBC Connect String property. A typical entry I've tried is
ODBC;DSN=SOTAMAS90;
UID=My_UID;PWD=My_PWD;
Description=MAS 90 32-bit ODBC;
Driver;Directory=M:\MAS90;
Prefix=M:\MAS90\soa\;
ViewDLL=M:\MAS90\Home\;
LogFile=\PVXODB32.LOG;
SERVER=NotTheServer

I've tried various strings, including ones generated by the ProvideX wizard. I usually get an error "Unexpected Extra Token".

Most threads approach this as a ADO / VBA issue, but I was wanting a simple query object for now. If the answer is to set up a standing connection in ADO and invoke that for queries, that would be fine with me.

Can anyone give me troubleshooting steps? How can I get a working connection?

[purple]If we knew what it was we were doing, it would not be called
research [blue]database[white].[/white]development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[BigLouie]

Your connection string in Access should look like this:

ODBC;DSN=SOTAMAS90;UID=XXX|YYY;PWD=ZZZ;Directory=\\account\account$\Best\MAS 200\Version4\MAS90\;SERVER=NotTheServer

Where XXX equals the user ID you type in when logging in, YYY equals the three digit company code and ZZZ equals the password which is case sensative. The rest is the UNC path to the server.

 

[GKChesterton]

No good yet. Am using:

ODBC;DSN=SOTAMAS90;UID=xxx|TFP;PWD=zzz;Directory=M:\MAS90;SERVER=NotTheServer

We have two companies, 006 and TFP; neither works. Instead I get:
ODBC--call failed.
[ProvideX][ODBC driver] unexpected extra token: ; (#1017)

Other ideas?

[purple]If we knew what it was we were doing, it would not be called
research [blue]database[white].[/white]development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[GKChesterton]

I also just tried using
\\Tfp-mas90-2003\Acct\MAS90\
instead of M:\MAS90 ... but no.

[purple]If we knew what it was we were doing, it would not be called
research [blue]database[white].[/white]development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[Kruken]

Just thought this might help its from the MAS documentation , I created my silent connect DSN using these steps ... Maybe steps 12 and 13 may be of particular interest in your case.



Appendix A Frequently Asked Questions
MAS 90 and MAS 200 Crystal Reports Manual
Creating a Silent Connect DSN
How do I create a silent connect DSN?
The silent running ODBC data source will run without prompting you to
log onto the database. This is necessary if you want to compile a report.
1 In Windows, click Start, Settings, and Control Panel.

2 Double-click the 32bit ODBC icon. On some systems, this icon
appears as "ODBC Data Sources (32 bit)." The ODBC Data Source
Administrator dialog box appears.

3 Click Add. The Create New Data Source dialog box appears.

4 Select MAS 90 32-Bit ODBC Driver and click Finish. The ProvideX
32-bit ODBC Setup dialog box appears.

5 At the Data Source Name field, type a new data source name (for
example, SOTAMAS90_silent). Do not use the name SOTAMAS90
as this is reserved for MAS 90 and MAS 200.

6 At the Description field, type a description for the data source.

7 At the Database Directory field, type the database directory
location; this is the path to the Mas90 directory (for example,
C:\Mas90). If you are using MAS 200, this may be the UNC path to
the Mas90 directory. This is the same path found under the
SOTAMAS90 Data Source.
8 Leave the Definition File field blank.

9 In the Optional Information section at the Company Code field,
enter a valid 3-digit MAS 90 or MAS 200 company code in
uppercase.

10 At the Default Userid field, type the default user ID in uppercase.
This is your MAS 90 or MAS 200 user code. It must be a 3-digit
user code. If it is a 2-digit user code, type the 2 digits followed by a
space.

11 Type your MAS 90 or MAS 200 password in uppercase.

12 At the Cache Size MB field, enter 4.

13 Select the Dirty Read and Burst Mode check boxes. Do not select
the Enable Debug and Key Restrict check boxes.

14 Click OK, and exit the ODBC Data Source Administrator and
Control Panel.





Steven Yvinec-Kruyk
Ubisoft Canada

http://www.ubi.com

 

[GKChesterton]

Thanks, Steven. I followed the method, except I couldn't locate Cache Size. I brought the string into my query, but when I tried running it I got "unexpected extra token." I then build another ODBC the same way, except I checked "silent mode" in addition to "Burst mode" etc. When I tried to bring that string into the query, I got a dialog box with title "Connection String Builder" and message "ODBC call failed." The property field would not even accept the string!

[purple]If we knew what it was we were doing, it would not be called
research [blue]database[white].[/white]development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[GKChesterton]

Thanks, Steven. I followed the method, except I couldn't locate Cache Size. I brought the string into my query and tried running it. I went through the dialog for UID and password, then got the "unexpected extra token" notice.

I then build another ODBC the same way, except I checked "silent mode" in addition to "Burst mode" etc. When I tried to bring that string into the query, I got a dialog box with title "Connection String Builder" and message "ODBC call failed." The property field would not even accept the string!

[purple]If we knew what it was we were doing, it would not be called
research [blue]database[white].[/white]development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[Kruken]

Unfortunatly im not using version 4 yet ... just upgrading now from 3.61 to 3.71 .... So we don't have the same version of the driver ... I will see if i can find any more info for you .. I have a call schedule with my vendor tomorrow morning , i will run the question by him.



Steven Yvinec-Kruyk
Ubisoft Canada

http://www.ubi.com

 

[BigLouie]

CK I have used that connection string with 4.05 and Access with no problems on a number of installs.

 

[GKChesterton]

Since last post I sought to ensure I have latest SOTAMAS90 driver. I have 3.33.10.00, dated 7/25/2004. It does work for Crys Reports and simple ODBC table linking to Access.

I wonder what is going wrong. I have also tried adding a space after my UID, which is 2 letters (upper-case).

Thanks and let me know if new insights arrive.

[purple]If we knew what it was we were doing, it would not be called
research [blue]database[white].[/white]development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[BigLouie]

my UID, which is 2 letters (upper-case). " AH HA! This is probably you problem. This is a very old issue. For some reason the ODBC driver does not like a 2 character code. Always use at least three (3) characters. Note that this is NOT your three digit user code but the actual User name you type in when you log into MAS.

 

[GKChesterton]

This is an exciting possible cure! I am waiting for my MAS 90 administrator to come through with a new UID. Will post resolution ...

[purple]If we knew what it was we were doing, it would not be called
research [blue]database[white].[/white]development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[GKChesterton]

Changing my UID to 3 characters solved this issue. And pass-through queries are fast. Thanks Big Louie and a Star!

Now I am discovering that SQL syntax is not precisely like Microsoft's. I will probably post on that ...

[purple]If we knew what it was we were doing, it would not be called
research [blue]database[white].[/white]development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[Kruken]

Its because of things like this that MAS drives me nuts sometimes . Glad you got your issue resolved.



Steven Yvinec-Kruyk
Ubisoft Canada

http://www.ubi.com

 

[BigLouie]

To get perfect SQL syntax when working with MAS and Access use Crystal. First open a new report based on the one table you want to pull data from. Then pull in all the fields into the detail section you want in your query. Then go to Database, Show SQL Statement. Copy the SQL statement and then in Access in the query editor paste in the SQL statement from Crystal. Things will be perfect.

 

[kanes]

OK, BL, but what if you want multiple tables, as is often the case?
Thx

 

[BigLouie]

As a rule you should have only one pass through query per table. You then write another Access query joining all the pass throughs together.

 

[GKChesterton]

Hello Colleagues,

I revisit this thread and find the discussion is developing helpfully. Thanks to Big Louie's other postings, I already understood somewhat that I should let Crystal Reports generate the SQL. But the real implications were yet to be discovered; for some hours I thought I was still having ODBC issues that were actually due to SQL syntax.

I got past that and today was able to benchmark some gloriously fast queries. I am now back to nagging our accountant for other potent query strings … since MAS 90 and its literature do so very little in documenting relationships.

I've done fine with some multi table queries. Here are three SQL entries:

Access 2003, using ODBC-linked tables (I deleted some parentheses):
[tt][small]
SELECT IM1_InventoryMasterfile.ItemNumber,
IM1_InventoryMasterfile.ItemDescription,
IM1_InventoryMasterfile.TotalQtyOnHand
FROM IM1_InventoryMasterfile INNER JOIN IM2_InventoryItemWhseDetl
ON IM1_InventoryMasterfile.ItemNumber = IM2_InventoryItemWhseDetl.ItemNumber

WHERE IM1_InventoryMasterfile.ItemNumber) LIKE "0*" AND
IM2_InventoryItemWhseDetl.QtyOnSalesOrder>0 AND
IM1_InventoryMasterfile.PrimaryVendorNumber LIKE "0130*";
[/small] [/tt]

The same as a Pass-Thru in Access; Crystal Reports generated SQL using table aliases:

[tt][small]SELECT IM1_InventoryMasterfile.ItemNumber,
IM1_InventoryMasterfile.ItemDescription,
IM1_InventoryMasterfile.TotalQtyOnHand
FROM "IM1_InventoryMasterfile" IM1_InventoryMasterfile,
"IM2_InventoryItemWhseDetl" IM2_InventoryItemWhseDetl
WHERE IM1_InventoryMasterfile.ItemNumber = IM2_InventoryItemWhseDetl.ItemNumber AND
IM1_InventoryMasterfile.ItemNumber LIKE '0%' AND
IM1_InventoryMasterfile.PrimaryVendorNumber LIKE '0130%' AND
IM2_InventoryItemWhseDetl.QtyOnSalesOrder > 0
[/small] [/tt]
(What the table aliases accomplish is unknown, aside from visual clutter, obviously.)

The same as above, but uses the JOIN keyword – and table aliases of my choosing:
[tt][small]
SELECT IM1.ItemNumber, IM1.ItemDescription, IM1.TotalQtyOnHand
FROM { OJ "IM1_InventoryMasterfile" IM1 INNER JOIN
"IM2_InventoryItemWhseDetl" IM2 ON IM1.ItemNumber = IM2.ItemNumber }
WHERE IM1.ItemNumber LIKE '0%' AND
IM1.PrimaryVendorNumber LIKE '0130%' AND
IM2.QtyOnSalesOrder > 0 [/small] [/tt]

Some syntax differences are "0130*" versus '0130%' and use of OJ keyword and { } brackets. Aliases are not declared without AS. I’m most interested in the odd join in version #2 – no JOIN keyword, which in Access would produce a Cartesian product. And how would one add a third table? If I take Big Louie’s advice, I should just build the root queries as pass-through and use native Access tools after that.

So why doesn’t the Web have a jist translator for SQL? Paste in your query, select a target application, and presto – the revised SQL is ready to copy!

[purple]If we knew what it was we were doing, it would not be called
research [blue]database[white].[/white]development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[GKChesterton]

UPDATE.

My goal remains to create a regular export of sales history data (history and open) from MAS 90 ver. 4, and hold it in MS Access 2003 where it can be compared with sales/quotes viewed as design projects. We have this separate database because we are somewhat engineer-to-order; thus we don't use MAS 90 for quotes, and also must compensate for its inability to organize sales on a higher project level.

So in Access, I replace the ODBC links to MAS 90 tables with pass-through queries (Crystal Reports helps me get the SQL rewritten for ProvideX). I can then use those in Access queries to build tools for refreshing some Access tables weekly or daily. Once I've done all that, the standing routine to normalize the results will be a huge achievement of its own.

I want to make sure I'm making the best use of ProvideX ODBC. So far it's impossible to get a history or summary of the driver versions. I have decided that 3.33 is probably good. The 50-page 3.32 manual it looks like the most useful thing so far for explaining the driver and SQL. It has the answers to questions I'd posted here (though NOT the precious detail about two-characters in the User ID). The manual is very sparse on SQL syntax. Instructions mostly phrased in tech-puke, few examples provided.

It would be great to know what version number is running on the accountant's workstation, and what's installed on the server; but to get this info requires management escalation. (This is due to interdepartmental hostilities. As Louie Armstrong said, "It's a Wonderful World.") Today is Monday, so I'll pursue that a bit later.

Thanks to all, and seeking your wisdom always!

[purple]If we knew what it was we were doing, it would not be called
research [blue]database[white].[/white]development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[BigLouie]

Please note that you are almost always on the correct ODBC driver for your version. It use to be that you could update the server but not the work station. That has since changed and now when you upgrade you have to re-run work station setup which keeps the driver current.

One thing that will bite you in the butt is the quirk of Access. If you are constantly refreshing the tables from MAS then the database will continue to grow in size until it hits 2 GB and stops working. Normally what I do is place the datatables in one database and link them into another database which contains the queries and code. I have a form with buttons that activate the code to empty the tables and repopulate. This keeps the databases at a constant size.