[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