Microsoft SQL Server versus ORACLE with ACCESS as the front end

[SallyStuart]

Has anyone had any good/bad experiences using ORACLE or SQL Server as the data base and ACCESS as the interface. Our current application is entirely in ACCESS and we are thinking we could get a major performance boost by switching to one or the other as the data base. Thanks

 

[mshinker]

My experience tells me the only way to connect Access to Oracle is through an ODBC driver. This leads to some very ugly stuff in terms of programming over in Access. You basically have to turn Access into a transactional system to post data to an Oracle backend database.<br>
<br>
Access connecting to SQL Server is a much better way to go. You can use DAO, ODBC and other methods to connect Access to SQL Server. You may want to check out the Access Upsizing Toolkit or Wizard its available at Microsoft's Web site.

 

[scoia]

I'm only giving you my experience but I'm working on a project with Access front-end and Oracle back-end. . . .it's working fine. We had it working first with Access back-end, but then converted the tables to Oracle. The entire application was designed from the start with the intent of moving to Oracle and it seems to be working really well and very quick. However, it is not a high-transaction system. . . most users are reading the records. Do you have many concurrent users on your system? Is that why you want to upgrade?

 

[calahans]

If you have a choice between Oracle or sql server for a back end choose SQL Server. Oracle is obviously a better RDBMS, however the microsoft link between the other two will lend to stability. <br>
<br>
We have installed a 10 user system with SServer back and access front-end. We get decent performance and stability.<br>
<br>
One word of warning, if you use DAO to access your data, you will be slowing down your system. We have used query objects in DAO and compared to using the execute method of the database object (with the pass through parameter) and the latter far outstrips the the DAO method.<br>
<br>
A chain's only as strong as its weakest link, can you take access out of the loop and use VB - ADO or RDO. The response time will be much higher.<br>
<br>
C