Create database & use it to import tables in mdb

[FuzMic]

Folks I finally is seriously moving to SQL server .. need a helping hand
I think of doing this with command as part of my codes in a FE.
I thus start with mdb as template to import from, into a to be created database in SQL server
What are the text commands that can achieve my goals.

 

[oleronesoftwares]

I thus start with mdb as template to import from, into a to be created database in SQL server

Are you referring to having a MDB database upsized to a SQL database?

 

[Minty]

I wouldn't do it this way around. In fact, I'm not sure you can create a Database in SQL Server from Access.
Tables and lots of other objects yes, but not the actual Database Object?

Create your main empty database in SQL server - use SSMS.
You can then use a variety of methods already discussed in your other thread to create your tables and populate things.

 

[Isaac]

in 2 past jobs I created "Create DDL" vba routines that looped through hundreds of access tables, examined the data types as well as (in some cases) the range of actual domain values (like lengths), created ddl for all sql tables to be pretty much just 'run' (after some judgment calls on identity/keys and tweaking) in ssms. but that's the downside of corporate jobs...you take nothing with you.
however, I might have sort of done so about 10 years ago. let me see if i can find it, if nothing else, it will be interesting to look at (and probably a little embarrassing in some way i'm sure!)

 

[Isaac]

Oh one more thing - I agree with Minty especially (if I understood you right Minty) - whatever you do, I do NOT suggest actually using the 'export to sql server' function that Access has (re: its tables).

Access makes sql datatype decisions for you that ... let's just say .... i would never make.
Get ready for a million ntext and n-everything columns. Totally ridiculous.

Better to study the issue and then YOU decide (informed-like) what sql datatypes YOU want your, say, Short Text access columns to become in sql server. Definitely don't let that Access wizard decide for you, Worst case it's dumb, Best case you didn't make your own decision.

 

[Pat Hartman]

Isaac and I disagree yet again. If you have the upsizing wizard available to you, it is the path of least resistance. I used it for years until they discontinued it and it worked fine. There might be an issue with bit data types or y/n data types but I never found any others. Otherwise use SSMA. It was broken for several years but I think it is fixed again. HOWEVER, you need to be careful with SSMA because it will change your date data types to something the default ODBC driver won't recognize rendering all your dates as text fields which will be very bad for you. So, BEFORE the conversion, be sure to check the mapping for data types to make sure your dates get mapped to datetime in SQL Server. This is not necessarily the most efficient mapping but it is the mapping that will always work no matter what ODBC driver you have available.

Make sure your source .mdb is clean. Make sure that you have proper indexes defined and all the defaults and properties set correctly.

Sometimes when I convert databases made by others I run into problems with dates because the original developer didn't validate anything so you might end up with 1/10/202 which Access thinks is a fine date but SQL Server balks at. So you might have data cleanup to do.

Usually, I can make a clean conversion in one or two tries. If the conversion isn't clean, I delete the db and start fresh.

Let the client's DBA define the database and backups. This is not your area of expertise. In most of my accounts, I always feel lucky if they let me work with a test server. When I'm done, the DBA takes over and ports the solution to the production server to which I do not want access because this is not my area of expertise despite using relational databases for over 40 years. I develop software. I am not a DBA. I know only enough to be dangerous. I don't want anyone blaming me for a backup not being available if there is a failure that requires recovery and neither do you.