Downsize to Access

[georgedwilkinson]

I am working on revamping a SQL Server database with an Access 2002 front end. I'd like to set up the environment on my computer at home but I really don't want to mess around with SQL Server variants on my computer (I am using Vista).

Is there a quick and easy way to downsize a SQL Server database to an Access back-end? Google always points me to up-sizing...I want to downsize.

 

[Banana]

I'd use Import to import them over. There'll be some post-processing such as specifying primary keys and indices.

If you really want something more automated and fixes up the keys, I _think_ dbConverter was the software you wanted to have and their trial may allow you to get what you need without that post processing hassle.

 

[georgedwilkinson]

I'll check out dbConverter. Thanks, Banana!

 

[SOS]

Personally, due to the fact that SQL Server is handled a bit differently from Access tables, I would just use SQL Server Express to have as a backend. Then you are really working on the frontend to fit with the real environment.

 

[georgedwilkinson]

I thought about that but I didn't want to invest a lot of time in the SS Express install. I am only doing this to reverse engineer the design of the database so I can design a replacement for it; I don't need anything like perfection or any need to use it after I am done with the analysis work. In order to do that, I need to see how the front end works, but I don't need to do tons of transactions. Eventually, I'll be doing my back-end development work (for the replacement) in SS 2005 on a dedicated server.

 

[SOS]

Then just delete the tables in your frontend and then IMPORT them instead of linking. The names should still be the same and you can just go from there.

 

[georgedwilkinson]

I'm kind of doing that, but no, the names are not the same when importing. There is the naming problem and there are a LOT of tables (I don't want to go in and rename the tables by hand). I'm also worried about some views and stored procedures I am using and of course, the referential integrity constraints (AKA foreign keys). I may have to do this multiple times so I'm writing a VBA sub to do it. I'll share it when I'm done (if such a state ever exists).

 

[Banana]

I take that dbConverter wasn't going to do the job?

 

[georgedwilkinson]

I got db converter (with a space). It is a gnu GPL thing. The google description said it converted MS SQL to Access but the program read-me says it is for converting Access to MySQL and Oracle.

When I force google to "dbconverter" (without a space), the definitions and sources of the files actually scares me (mentions of warez, cracks, torrent, keygen, etc.). I usually don't go to sites like that (as my hard drive is "pure"). Another option is "dbconverter.dll" which doesn't really "seem" to be in the spirit of what I need. Also present is a program that repairs database files (doesn't seem to fit).

Keywords: "dbconverter" -warez -crack -torrent -keygen
don't seem to return anything that does what I need, though.

 

[Banana]

This is what I was referring to: http://dbconvert.com/convert-access-to-mssql-pro.php

My bad - I thought it was a -er, but it didn't have that suffix. Note that trial allows you to convert the structure but you don't get more data beyond 50 rows and wants you to cough up $80 for the full functionality but you could try it and get the structure loaded.

I used that for a different project (I want to say Access -> MySQL but I'm not sure) and it worked well.

 

[dportas]

I thought about that but I didn't want to invest a lot of time in the SS Express install.

I don't quite see what the problem is. SQL Server Express takes less time to install than it must have taken to type your messages in this thread. Even SQL Server Dev Edition takes only a few minutes for a minimal install. Then you will be able to work with the actual database instead of wasting time doing a messy and unreliable conversion.

If you are eventually going to develop on SQL Server then a local install is very useful anyway. I wouldn't want to be without it if I were you.

 

[georgedwilkinson]

I don't quite see what the problem is. SQL Server Express takes less time to install than it must have taken to type your messages in this thread. Even SQL Server Dev Edition takes only a few minutes for a minimal install. Then you will be able to work with the actual database instead of wasting time doing a messy and unreliable conversion.

If you are eventually going to develop on SQL Server then a local install is very useful anyway. I wouldn't want to be without it if I were you.

Your logic is impeccable. I do question your estimates, though. Time spent on this thread is pushing 10 minutes. Time trying (unsuccessfully) to install SSE this month is around 4 hours.

I'm not sure if I can sell an install of SSE to my personal PC to my client, but I can sell a re-usable program that they can see that has other utility.

But you are totally right about this...just not sure how I'm gonna pull it off since I should have already had SSE months ago (brainless install, right?) but I still can't get a clean install.

 

[dportas]

Have you asked for help in a SQL Server forum like SQLServerCentral.com? I've installed SQL Server Express many times and I don't think it has ever taken more than 10 minutes.

 

[georgedwilkinson]

Yeah, I finally got it fixed about 1:00 AM this morning. Seems you cannot install SSE on a computer that has the same name as the installing user name.

There are features not covered in SSE (according to MS literature) that will make this take a lot longer than one would think. The system I am working on makes extensive use of views and according to the literature, views don't exist in SSE. I know there are no views in Access but you can import a view. Anyhoo, I gotta figure out how I'm going to deal with the views and stored procedures, regardless of how I go forward.


Also, to use SSE, I guess I'm going to have to install a tool like the SSE "Import and Export Data" somewhere in the network where the production database lives (leaving me right back where I started, if I go with SSE). How do other developers deal with this? Is there a tool to easily convert a SQL Server database to SSE format without having to install the whole suite on the network?

 

[dportas]

Glad you got it working. SSE definitely does support views. Even indexed views are supported (you won't get query rewrites against indexed views but that isn't likely to be a problem in Express).

No import conversion is needed for Express because it uses exactly the same file format as other editions of SQL Server. You can just copy or backup the database from Standard or Enterprise edition and then attach or restore it without modification if the database is less than 4GB.

However, if you are developing for Standard or Enterprise SQL Server and you want dev tools, integration services (for import or export) or other standard/enterprise features then you are better off purchasing Developer Edition instead. Developer Edition costs around $50 and gives you the equivalent of Enterprise Edition for development and test purposes only. For development purposes it is much better value than Express.

If you stick with Express then you will probably want to download Management Studio for it:
http://msdn.microsoft.com/en-us/library/ms365247.aspx