Steve Turner
sturner at mseco.com
Fri May 22 09:33:52 CDT 2009
John, I have had the same problem using SQL Express with ODBC connections to a Pervasive Database. We have a custom timesheet program written in VB6 and data stored in SQL Express. We have to copy the same Access 2k database for the three users that do the Access reports from the SQL db. We link to a GL data in Pervasive to get expense data from there. I found that you have to set the DSN names to be the same on each machine when creating the links on each machine. Pervasive has a management tool that will let you find the DSN's and delete them if necessary. Every now and then the link to a table will break for no reason and you have to relink it to get back to going. The biggest problem is trying to write a new VBA code or a query with someone else in the database. They have to exit for you to save. Running reports are no problem. We use XP SP3 on the workstations with Server2003 on the servers and Access 2000. Also if someone writes a new report or changes a query you have to copy it into your copy of the db. This is a screwy workaround but we get by. Steve A. Turner -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: Friday, May 22, 2009 7:25 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Linked to SQL Server in a domain Jim, The client is a very small company (6 or so employees) that helps seniors with their medical insurance plans, researching billing issues and so forth. They wanted a new network administrator (which I am definitely not) so they went out and hired a new company to do this. This company advised them that in order to be HIPAA compliant they needed to move the data into SQL Server (for encryption reasons AFAICT). It seems this company quoted a very low ball price to convert the system to SQL Server, and in the end it APPEARS that all they did was run the upgrade wizard. AFAICS out in the SQL Server there are no views or stored procedures at all, only the data. Even there they appear to have screwed some stuff up. The database, which I did not write but which I maintained and added new functionality to for many years, was working fine (as stated by the company owner) before the upgrade but has "a bunch of problems" since the upgrade. The owner is in the process of hiring a new network admin company and has asked me to come back and straighten out the problems in the db. To be honest I was just brought back in a few days ago so I really don't know the details yet on the "bunch of problems". In any event, the DB FE uses simple ODBC links to get at the data. They have very few employees and performance seems to be pretty adequate doing things this way. I understand (form a theoretical perspective) the concept of using ADO recordsets for the bound forms and as data sources for the combos etc. hitting stored procedures out on the SQL Server but none of that is presently used. I am trying to come in and get some additional functionality happening quickly, for example a fairly strong login to the database and possibly additional presentation layer security. I really don't want to get bogged down in trying to move the database away from ODBC at this point, though I would love to take this opportunity to do this as things settle down. John W. Colby www.ColbyConsulting.com Jim Lawrence wrote: > Hi John: > > I have been working with MS Access to ADO-OLE to MS SQL/Oracle DBs since > 1997 and Access does not work with ODBC. There is a simple and stupid way to > up date an old Access MDB to a MS SQL BE using connection/links. > > This system actually works fine for delete, add and update... but as soon as > you start grabbing recordsets of data for reports, subforms or start rolling > out a new application to a remote desktops the whole system grinds to a > halt. At that point you have reached the end of the usefulness of the ODBC > connections. > > Unfortunately there is no short cut, the client and you just have to bite > the bullet and go straight ADO-OLE... It is not that difficult but the nice > gui interface within Access can not do it. > > Jim -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com