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

 

[Databaseguy]

Big Louie, you can also just do a compact and repair on your Access database. That'll shrink it back down.

Access also does that when you're doing a lot of design work. Adding and deleting forms, queries etc.

The good news is anyone can create an Access database.

The BAD news is anyone can create an Access database.

 

[GKChesterton]

Agreed, Databaseguy. Access' blessing is its curse ... and those of us who have been thrilled to learn RDBM principles using it (and sometimes made a living cleaning up toxic Access debris) must also live with the exaggerated rumors of its weaknesses. But BigLouie's observation is very well taken. Access bloat during migration is bound to sandbag some users.

My migration is going pretty well. I learned how to use @@IDENTITY to pick up a newly-coined primary key. Now I'm combing over the MySQL table designs, as the automated conversion made some wrong choices (forcing me to learn what the right choices ought to have been ... ).

[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]​