Steve Turner
sturner at mseco.com
Wed Apr 2 11:30:26 CDT 2008
Mark, Pretty close. In the same MDB if we tried to use it and create a link you couldn't get back to the file from another machine once the link was broke. Even using the same names exactly. In the ODBC admin we create a DSN name and create a Database name to show where the files are located. Using the exact name on each machine. Wouldn't work once someone else broke the link. So we copied the database and renamed it so each machine could open it seperately. Used same DSN and Database(file location db) names on all machines, using simple names like MSE and MSE2. You would .think that it would work but it took me two days of trying and deleting the same Database names to get the connections to work. Luckily the Pervasive software has a control center that lets you look at the Database names to the files, and show's what files and tables will open, and delete them there. You couldn't delete them from the ODBC admin tool from windows. Last year before I found that control center I had 50 or so names out there trying to get a link to work. Its really a baffling problem. Steve -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark A Matte Sent: Wednesday, April 02, 2008 11:06 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] newbie trying to get in a post. Steve, This might not be the problem...but I have seem similar occurances with a shared MDB that linked to an external datasource. When the group started using the MDB they had to manually create the ODBC on each machine (since fixed my having same ODBC's automatically created on every machine). The problem came from the description of the ODBC connection. In creating the ODBC on a machine you have a field called "Description"...basically whatever you want to call it. This example is a single MDB on a shared network used by multiple people: On the first computer the ODBC Description was 'MyExternalDB'. Everything works fine. Second Computer creates the ODBC using description 'MyExternal DB'. If the second computer refreshes the linked table with his ODBC...the first computer's links will no longer work. Notice the only difference in the descriptions is a 'space' between External and DB. When we go back to each computer and ensure that each ODBC description is exactly the same on every pc...refresh the links...then everyone is ok. This may not be your issue...shared just in case. Thanks, Mark A. Matte > Date: Wed, 2 Apr 2008 10:33:30 -0500 > From: sturner at mseco.com > To: accessd at databaseadvisors.com > Subject: Re: [AccessD] newbie trying to get in a post. > > 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 > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com _________________________________________________________________ Use video conversation to talk face-to-face with Windows Live Messenger. http://www.windowslive.com/messenger/connect_your_way.html?ocid=TXT_TAGL M_WL_Refresh_messenger_video_042008 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com