Access GUI and MS-SQL server

Posted on 2003-02-21
Medium Priority
Last Modified: 2006-11-17
I got some Access databases that I would like to convert to SQL.  All these databases have form built into them to input the data (the GUI).  How do I use the same GUI in access but have SQL as the actual container of the database and the one that runs all the queries and stuff?  Thanks for your patience :).
Question by:palpullero
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7996250
The easiest way (and also the cheesiest) is to link the SQL tables to an Access database and use them pretty much like any other Access table.


Accepted Solution

Divaqs earned 150 total points
ID: 7996360
Hi palpullero,

There are a lot of possible ways to do this. As Anthony (acperkins) suggested the easiest is to link the SQL tables to the Access database (File ->Get External Data->Link Tables->File of Type->ODBC->Then Select or Create DSN to SQL database, then select tables after establishing connection link).

You can name the linked tables to be the same name as the previous access tables (you will need to name the access tables something else or delete them) so that the forms and VBA code will go to the correct tables.

Other possible things to look into are:

Create an Access Project that uses the SQL server as its database and copy the form/VBA code into it.

Use "Pass Through" Queries in Access that connect to stored procedures on the SQL server

Use ADO in the Access VBA/Form code to connect to the SQL server.

The issues with one option or another is whether you can use a machine specific DSN connection or not. If the Access database needs a DSN-less connection to the SQL server (such as a database that is moved from one computer to another) you will need to use something like ADO and/or Pass Through Queries.
LVL 75

Expert Comment

by:Anthony Perkins
ID: 8676817
Do you need any more help with this question?


Author Comment

ID: 8683740
Thanks to Anthony and Divaqs.  I'll be using Access Projects to get my dbs into SQL and still use the same GUI.  Thanks guys!

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question