x Want to participate? Join the group to interact
Options
  • State Suggested Answer
  • Replies 7 replies
  • Answers 1 answer
  • Subscribers 107 subscribers
  • Views 1962 views
  • Users 0 members are here
Browse Forums
Announcements
293
topics
370
replies
Business Object Interface
1k
topics
7k
replies
Core Financial Modules
2k
topics
8k
replies
Customization and Productivity
3k
topics
12k
replies
Distribution, Manufacturing, and Applications
2k
topics
7k
replies
General Discussion
1k
topics
3k
replies
Partner Cloud
45
topics
91
replies
Payroll, Time Tracking, and Job Cost
777
topics
1k
replies
Reporting and Analytics
962
topics
3k
replies
Sage CRM for Sage 100
164
topics
357
replies
Technical and Installation
2k
topics
9k
replies
Year-end Forum
89
topics
237
replies
jcbaker

ODBC for VI using Access Split Database

SUGGESTED
Posted By

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.

Parents
  • 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.

Reply
  • 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.

Children

*Community Hub is the new name for Sage City