Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 274
  • Last Modified:

Access GUI and MS-SQL server

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 :).
0
palpullero
Asked:
palpullero
  • 2
1 Solution
 
Anthony PerkinsCommented:
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.

Anthony
0
 
DivaqsCommented:
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.
0
 
Anthony PerkinsCommented:
Do you need any more help with this question?

Anthony
0
 
palpulleroAuthor Commented:
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!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now