ODBC for VI using Access Split Database

SUGGESTED

Hi all, would like a better understanding of best practice in setting up an ODBC link for Sage 100 VI jobs. We are currently still on Sage 100C Advanced 2018, and I am working to beef up what began as a home-grown Access solution to meet a specific accounting need, that has now become a full program in its own right. It's to the point now that I need to split the Access database but am a little confused as to the proper settings.

Based on what I found in the forums I thought I could point my ODBC link direcly to the back end, but when I attempt to use that as the VI Data Source I get "Unable to open [wdx][ODB]databasename". If I point ODBC to the front end I do not get the error, but none of the tables I need to source are listed because they reside in the back end.

I suppose I could create queries in the front end to access the data in the back end, but I question whether that is the most efficient method. Any suggestions would be welcome.

  • 0

    Question # 1 - Why do you need to split the Access database?

    Question #2 - If I understand you correctly you have some linked tables in an Access database that you use as data source for some VI jobs, is this correct.

    Question # 3 - Have you considered programming in the Access that could create a table or text file and have that as the source of the VI job.

    Suggestion #1 - Use SQL Specific Pass Through Queries instead of linked tables.  

  • 0 in reply to BigLouie

    I'm assuming this is a multi-user Access application and so the best practice is to place front end forms in one database and keep the actual data tables in the backend database. As for accessing from sage 100, i've created linked tables in my front end with a DSN set up to point to the front end. If i remember correctly though, this had to be a separate front end from the the one used by the user accessing the app, so i had one with a UI suffix and another with a QRY suffix. Had to to do with the way the jet driver would lock databases/queries/tables.

    Passthrough queries might get around the locking but i'm not positive, for my project, i had to use linked tables to benefit from the indexes.

  • 0 in reply to BigLouie

    Hi Louie! Thanks for the quick response.

    1. We have multiple users now getting in to the Access database so I would feel better if the database were split to avoid data corruption (not to mention my own convenience: without a split database I am unable to make design changes without kicking everyone out or working late hours).

    2. Yes. This database pulls data from Sage 100 for further analysis in conjunction with other data sources. Several resulting Access tables are then used to import data back in to Sage 100 via VI, for instance to Cash Receipts or AP Invoice Header. Before splitting the database the tables it worked fine--but as described in my initial post, if I point ODBC to the front end I can't see the tables in VI because they are now just links pointing to the back end. For that reason I had hoped to set up ODBC to the back end but I am getting that error in VI.

    3. I have done that before but frankly find it a cumbersome extra step that has not been required up to this point. I have more than a dozen VI jobs already written that can fairly easily be pointed to a different ODBC Data Source. They would have to be completely rewritten if changing to csv or xlsx, so that would be my absolute last choice.

    As to your suggestion... do I understand correctly that you are saying to go ahead and set up ODBC to the front end database, and then create queries to the back end tables? So then VI will link to the front end but I'll have access through the queries to the back end source tables? I was afraid that would be the answer but I like that better than redesigning to export to text files!

  • 0 in reply to David Speck

    Actually I was thinking along the lines of creating a separate front end that just contains queries needed by Sage VI. Each user has their own front end already so it won't be a problem to create another for user "Sage100".

  • 0 in reply to jcbaker
    SUGGESTED

    I always suggest Pass Through Queries because they return data faster than linked tables.  A Pass-through query allows you to execute a SQL statement directly against the tables in Sage 100. This method uses your ODBC connection to directly access the data in the external database without having to link to the tables.  Don't forget that tables get locked in Sage 100 during certain process so this is why linked tables might return an unable to open error while a pass through will not as it is pulling specific records. 

     if you are dealing a table with several hundred thousand records and you want only records that meet certain criteria, you gain a tremendous advantage by using a pass-through query.

    Why? If you write a query on a linked table and set up criteria, each record is brought from the server to the client to be evaluated, resulting in quite a bit of network traffic. If records are locked by Sage 100 while an update is happening you will get your error message. If you write a pass-through query, the query instructions are passed to SQL Server and the query is evaluated on the server. Only data that meets the query criteria is passed to the client. 

    I have posted how to create Pass Through Queries before.

  • 0 in reply to BigLouie

    You are probably right I could improve speed using pass-thru queries in some cases rather than linked tables, since I don't always need to take advantage of the existing indexes. I have used PTQ for years with certain complex Crystal Reports, but I will definitely look in to using them more in this application.

    My difficulty now is specifically to do with the split database configuration: I created a new front end and pointed my DSN to it for use by Sage 100. I then linked to the appropriate tables on the back end and set up a query to pull the data for the import. I can see and select the query in Sage VI but when I click on the Fields tab there are no fields listed. Attempting to write the SQL statement (skipping field selection) resulted in an error.

    If I switch the DSN back to the "unsplit" database and select a similar query, in VI I can select the query and see/select the Fields to include. So at this point I'm stumped. Not sure if it has to do with Access versions as I'm also wanting to bring my old databases up to the latest accdb format, but I don't think that's it--everything points to the links to the back end database. 

    Very much appreciate the suggestions--keep them coming!

  • 0 in reply to jcbaker

    By way of follow up I rolled my Access database back end back to true 2003 mdb format and I can now successfully link direct to the back end via ODBC to VI--no errors. While I have successfully used the newer accdb format to import to Sage VI, apparently during the split database process something changed in the back end (besides saving as .accdb) that Sage VI doesn't like. This will work for now though I would prefer to bring the back end up to the most recent format as well, so if anyone learns a trick to it please share. Thank you for all the input!