Solved

MSAccess frontend and SQL  backend

Posted on 2014-03-10
9
634 Views
Last Modified: 2014-03-25
Hi all,

I would like to learn more about using SQL as my backend. I am quite sure this is something very normal for databases with a huge numbert of records and it is somethin I am keen to experiment with.

I should therefore be grateful if someone would point me in the right direction because every time I try to download the trial free version it asks me to select from a raft of components which I have no idea of.

Also, I am using Win7 as my OS on a 64 bit laptop.

Comments appreciated.... thanks
0
Comment
Question by:PipMic
  • 2
  • 2
  • 2
  • +2
9 Comments
 
LVL 8

Assisted Solution

by:jpgobert
jpgobert earned 70 total points
ID: 39918748
Why not start with SQL Express if you're just trying to learn?  You can download SQL Server 2012 Express from here:  MS Download Center - SQL Server 2012 Express

When you click on Download you'll have a list of downloads to choose from.  Just choose the first one, "SQLEXPR_x64ENU.exe" and also "SQLManagementStudio_x64_ENU.exe".

The first is the actual installer for SQL Server 2012 Express.  The second is the installer for SQL Management Studio.
0
 
LVL 84
ID: 39919200
Essentially all you need with SQL Server and SSMS from the links above.

Once you have those, you can open SSMS and work with the databases - you can add a New database, modify an Existing one, etc etc. SSMS is kind of like a cross between the Access Table and Query designers, with lots of other stuff built in.

You'd then link your tables on the server back to your database in Access, and you should be off to the races.

There are some differences - for example, SQL Server does not have a Boolean field, and instead uses the Bit datatype for that. Often your Access database will have troubles with this, so you've got to do a bit of research.

MSFT offers a SQL Server Migration Assistant that can help you move your data up to SQL:

http://www.microsoft.com/en-us/download/details.aspx?id=28763

While Access includes the "upsizing wizard", in general you'll get better results using the SSMA.

Here's some other resources that may help with your conversions:

http://www.fmsinc.com/tpapers/index.html#SQLPapers
http://www.jstreettech.com/downloads.aspx - the Best of Both Worlds
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39919515
When you install SQL Server Express for testing, you can safely take the defaults.  If this is something you will use for a production application, you are better off paying someone to set it up to your specifications.
0
 
LVL 41

Expert Comment

by:pcelba
ID: 39920035
Wow EE is censored...

Even my paragraph saying "You may link SQL Server tables from MS Access and work with then as if they were Access tables" was removed...

Serious admin would keep the track of this change in the question itself but that's not EE case... Where is the (EE) World going to?


Here is the post from admin:
I've removed your comment in the thread above, since it was little more than links to competing sites.

Please see this Help article:

http://support.experts-exchange.com/customer/portal/articles/1162518

Essentially you're expected to provide full details of your suggested solution directly in the question thread. The use of links should be used to support your own suggestions, not to directly answer the question.

SouthMod
EE Moderator
SouthMod@e-e.com
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:PipMic
ID: 39921760
Thanks guys,

But do i have to install .net software?
0
 
LVL 41

Assisted Solution

by:pcelba
pcelba earned 50 total points
ID: 39921775
No, not at all.

Install just the SQL Server Express + MS SQL Management Studio Express.

SQL Server Express is the data engine (running as a Windows Service) and Management Studio allows to manage the data at administrative level (user rights, database setup and maintenance, query tools etc.)

After you'll do the SQL setup then you may use just MS Access to handle SQL data as linked tables.

Basic knowledge of SQL language is essential to do this task.
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 50 total points
ID: 39922396
But do i have to install .net software?
You do need the correct .NET frameworks installed, but in almost every case those are already present on the machine.
0
 

Author Closing Comment

by:PipMic
ID: 39952935
Good combination of info.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

758 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now