Converting an MS Acces DB to a SQL DB

cdnqte

Board Regular
Joined
Jul 14, 2004
Messages
132
Is there an easy way to convert my access database to an SQL database?
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
No.

What research have you done on this so far, or is this your first piece of research. That will help a lot on how to advise.
 

MyBoo

Board Regular
Joined
Aug 9, 2006
Messages
217
Vic,

There is an easy way to convert an Access db to an SQL db... upsize the database using Tools/Database Utilities/Upsizing Wizard. If not already installed, this will install an MS SQLServer Manager. You will still be able to use MS Access to access this data but it is now in SQL format.
 

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032

ADVERTISEMENT

OK, that is "easy" but have you really converted a database to SQL Server, or just converted the data, and now you have a lot of work to do it "right"? That was my reason for say No, because I see much more to the project than just getting the data into SQL from Access, even if you are going to keep using Access as your front end.

I am currently in the middle of a project of doing just that, and by converting to Stored Procedures, etc, we have gained a tremendous amount of extra speed in the response from SQL as just doing the initial "conversion."

But, yes, I will agree, the Tools/Database Utilities/Upsizing Wizard is a very easy step.
 

dh_ander

Board Regular
Joined
Jul 31, 2006
Messages
119
Use of the upsizing and converting might be easy.
However I have great problems using my modules who now have to refer to the SQL tables.

They are not automatically updated and you have to do the recoding yourselve.
 

MyBoo

Board Regular
Joined
Aug 9, 2006
Messages
217
Although, I do not have extensive experience converting MS Access to SQL and I'm sure there are many issues involved that I have no understanding or knowledge of, I have converted tables to SQL and linked them to my MS Access application - as I would any other external database that Access is compatible with and I have had no problems using my existing forms, queries, reports and/or modules.

The user asked if there was an easy way to convert his access db to SQL. In my experience, if my database forms, queries, reports and modules work in MS Access format -before I upsize, I see no reason why it shouldn't work in the same manner after I've upsized my tables and linked them to my Access application. Based on this, I responded to the question and I still stand by my statement.

Are there acceptions? Most likely.
Should I have responded that you have to be careful, not everything will work the same after upsizing? It's not my experience, so.. no, I don't think I should have said that.
 

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
David,

I'm assuming you are refering to the fact that Access names SQL tables with the dbo_ in front of the names we already gave these tables. What I do is write a very quick and easy rename routine that only looks at tables that start with "dbo_" and then rename them without the "dbo_".
Saves a lot of time.
HTH,
 

Forum statistics

Threads
1,137,195
Messages
5,680,095
Members
419,880
Latest member
suarezprado

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top