0

Back Story I am working with SAGE Mas 200 software, trying to link to it through Access. This software houses date for 300 investors for my company. With EACH investor I have a different investor ID that goes along with my UID and Password upon logging in, therefore EVERY connection string is different.

After many hours of trial and error, I have been successful in connecting via Access to only find out that I can only have one active connection string per database. Since I have 300 investors that I need to pull data from, there is the problem...and I can't figure it out.

I am a newby with VBA so my knowledge is extremely limited. I have read about linking to a 'template' file with the connection strings, but not sure how to format the template file. Can someone show me an example? Also, how would I get it to loop thru each query stored in the database and then pull the appropriate connection string?

Examples Here is an example of my connection string where 'AAL' is the investor code: 

ODBC;DSN=SOTAMAS90;UID=tbard|AAL;PWD=password;Directory=\\mas-200\MAS90;Prefix=\\mas-200\MAS90\SY\, \\mas-200\MAS90\==\;ViewDLL=\\mas-200\MAS90\HOME;SERVER=NotTheServer

The query name for this would be MAS_AAL.

Another investor: SMP, the query name MAS_SMP. Etc, etc, etc...

Can someone be my light at the end of the tunnel and help me out so I don't have to manually run 300 queries. I'm dying over here!!

Samuel Lelièvre
  • 3,212
  • 1
  • 14
  • 27
Tonya Ballard
  • 1
  • 2

1 Answers1

0

You can programmatically set the connection string.

There is a MAS 90 / MAS 200 ODBC driver called SOTAMAS90. On MAS installations in the last 5 years, there is both a 32 and a 64 bit driver.

In VBA, if I remember correctly, you would do something like this. (This assumes you already have fetched the Investor object you need, and it has properties called username and password):

Dim conn as new ADODB.Connection()

' Set properties of connection string manually
conn.ConnectionString = "DRIVER = SOTAMAS90; UID=" & myInvestor.username & "; PWD=" & myInvestor.password & "; Company=ABC"

conn.Open()

Another approach is to use a single ODBC connection within Access, to set up a linked ODBC table, using Access' Get External Data feature. Then you can treat it like any other Access table, and just query it directly. The problem with this approach is that Access will keep prompting you for credentials, both when you first open the table, and then if you are inactive for a while. I'm told you can save the UID and password in the SOTAMAS90 ODBC connection, but I've never done it.

Hope that helps.

Aaron

FunkMonkey33
  • 1,956
  • 2
  • 16
  • 24