Steve Turner
sturner at mseco.com
Wed Apr 2 10:33:30 CDT 2008
Thanks John, I just found that out when the VB programmer changed some tables in the Time Sheet Program and I couldn't see them. I think the biggest problem I'm having is with the Pervasive ODBC Engine that links to the Accounting Software. I'm still waiting to talk to someone from Pervasive about their ODBC driver. After much trial & error we found that the only way to keep each other from breaking these links was to make copies of the .mdb for each of us to use and sometimes even that's a pain when they change the Accounting file and we have to relink those. Steve -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: Wednesday, April 02, 2008 10:04 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] newbie trying to get in a post. Steve, It has been my experience with linking Access to SQL Server via odbc that when ANYTHING changes in a table or view, the link has to be deleted and recreated. Somehow the data will continue to appear as it used to be, for example if you added a field to the table or view it would not be displayed until you deleted the link and recreated it. A major PITA but that is what I do. John W. Colby Colby Consulting www.ColbyConsulting.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Steve Turner Sent: Wednesday, April 02, 2008 10:26 AM To: accessd at databaseadvisors.com Subject: [AccessD] newbie trying to get in a post. I have a small aggravating problem with MS Access and the ODBC links to a Pervasive Database in CYMA Accounting software. We are an Engineering Firm using a custom timesheet written in VB6 and using SQL Express. There are three people using Access 2000 to link files from SQL Express, Excel spreadsheets, and the Pervasive files in CYMA. We have been working with two copies of the database to keep each other from breaking the ODBC Links to Pervasive. We have just upgraded to a new version of the CYMA accounting software and of course they changed a couple of tables we linked to in Access. My computer was able to reconnect to the Tables; however I am having problems getting the other computers to create a link. It will link to some of the Tables but not all and of course the one Table we can't link is the one we need the most. Last year we had the same problem when we upgraded to the newest Version of accounting but after much trial and error was able to get the computers all working. What the ODBC connection program is doing is in no way logical. On my machine when we found the broke link. I just went in and created a new database name and linked to one of many old database names in pervasive that link to the Accounting files. I was able to see the new data with no problem. Went to the other two machines and tried that but couldn't get the link to work. I copied My Access database and renamed it so it would be opened by the second machine. In pervasive there is a control panel that lets you delete the old Database connections and you can see the tables each is accessing. Wiped out the database names on that computer and recreated them using the same names as mine. Last time we found this was the only way to get a connection. When we create the DSN in Microsoft's ODBC manager the test connection is ok. When we try in Access to create a link some Tables we can link to and some will not. On this other computer I finally got some success, I found that if I tried to open the link it would break, but if I opened a good link first then I could then open the other table. Doesn't make sense. Also found out that if you tried to open the link and you got the error code that if you went and linked the data again without selecting any specific table and saying OK you could then open the Table. Go figure. Now on the third Machine we use another copy of the Access database but there are many queries and reports not on the other two that we need. I can get all but one Table we need linked and this one keeps giving the error code when trying to access from this machine. Tried everything I did on the others but to no avail. I was hoping that someone might have run across this little problem or know someone who has that can help me solve my little problem. I'm having no luck with CYMA or Pervasive or Microsoft's Knowledge Base on it. We are all running XP with latest service pack, the same version of Access 2000 and the server is Windows 2003. I even went to Pervasive's web site and downloaded a utility to create ODBC DSN's and it created a new database in ODBC but it wouldn't link either. PS: I finally was able to get the third machine to see the file but how it happened I don't know, I just kept trying to link and then all of a sudden it worked. Steve A. Turner Controller Mid-South Engineering Co. Inc P.O. Box 1399 Hot Springs, AR 71902 E-Mail: saturner at mseco.com Phone:(501)321-2276 Fax: (501)321-4750 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com