VB and Mas90 ODBC Connection.... 3

[swhitlow]

I was trying to access a Mas90 system using ODBC. I've been successful in connecting but I have one problem. The login screen keeps popping up whenever you connect. I was wondering if there is anyway at all around this. Here is my current connect string:

objConn.Open "DSN=SOTAMAS90;Directory=\\fileserver\acctapp$mas90;SERVER=fileserver"

I've also tried this connect string:

objConn.Open "DSN=SOTAMAS90;Directory=\\fileserver\acctapp$mas90;SERVER=fileserver;UID=username;PWD=password;CompanyCod
e=companycode"

But I haven't had any luck. I need to pass the username, password and company code and not have the login screen to come up. Any suggestions? Thanks!

 

[BigLouie]

Linked? *ugh* You should be using SQL Specific Pass Through Queries. Here is how:

The following example uses MS Access to create a database that will use linked connections to the SO1_SOEntryHeader but you can substitute the SO5_InvoiceDataEntryHeader table if you will be pulling from the invoice files. These tables are used primarily when creating shipment maps. Although MS Access is used here as an example, any database application could actually be used.

Note: Knowledge of MS Access is required. Best Software cannot assist in this task

1. Start up Microsoft Access. The steps below will vary depending on version of Access and personal preferences. Click on File, New and choose Blank Database. Save the database on a local drive or in a location of your choice.
2. Open Crystal and start a new report
3. Base report on ONE MAS200 table, in this case SO1
4. Pull into the detail section all fields you want
5. Go to Database on the menu
6. Go to Show SQL Statement
7. Highlight and copy the entire SQL statement.
8. Open your Access application
9. Go to Queries
10. Create New Query
11. Close table box that opens.
12. Change Query type to SQL specific
13. When edit window opens paste in SQL statement from Crystal
14. Go to View on menu
15. Go to Properties
16. You will need to type in the connection string as follows because the ODBC connection Wizard in Access will not work with the MAS 4.0 ODBC driver in many cases:
ODBC;DSN=SOTAMAS90;UID=XXX|ABC;PWD=XYZ;Directory=\\account\account$\Best\MAS 200\Version4\MAS90\;SERVER=NotTheServer
17. Where XXX is the user ID, ABC is the company code and XYZ is the password. The rest will be based on your system. Note there is a | between the user ID and the company code.
18. A very very important note is on the ODBC connection string, make sure that DirtyReads=1 and BurstMode=1 is removed. It is critcial to omit these options in the connection string.
19. Save.
20. You now have a working pass-through query with auto-login. This method returns data faster than a linked table and allows you to connect to MAS without having to create a silent DSN.

 

[RichS]

First: A quick hello to BigLouie...

I don't expect mpemberton to still be researching this but I'll add this into the post anyway.

As to the date issue: "where CheckDate = #12/20/2001#" - MAS90 expects dates in a different format. I'm going from memory here but I believe it expects the following: #2001/12/20#. I remember wrestling with this and it was not apparent what format to use.