[AccessD] newbie trying to get in a post.

jwcolby jwcolby at colbyconsulting.com
Wed Apr 2 10:04:05 CDT 2008


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 

 





More information about the AccessD mailing list