[AccessD] newbie trying to get in a post.

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




More information about the AccessD mailing list