Up sizing an mdb to SQL server 08

[FuzMic]

Hi folks
I use mdb as the backend. I will like to import this database to SQL server. If i just import using SQL import function I end up with all tables. I like to keep together in one database as in mdb .. how to get there

 

[GPGeorge]

It's not really clear what the problem is. All relational databases contain one or more tables, regardless of which RDBMS you use. If you have five tables in an mdb, and migrate them to SQL Server, you'll still have the same five tables. Maybe more details as to the nature of the problem will help.

 

[MajP]

My guess is that your queries were also converted to tables. Is that what you mean?
If you need to convert an Access query to a SQL view / query this works very well.

SQL Converter - AccessUserGroups.org

Convert your Access/Jet SQL queries into T-SQL to run on Microsoft SQL Server. It supports converting the most commonly used functions.

accessusergroups.org

 

[GPGeorge]

My guess is that your queries were also converted to tables. Is that what you mean?

That could happen. If the naming convention used in the Access accdb was not clear, the only differentiator would be a pretty obscure icon:

 

[Isaac]

Hi folks
I use mdb as the backend. I will like to import this database to SQL server. If i just import using SQL import function I end up with all tables. I like to keep together in one database as in mdb .. how to get there

Your access tables were converted to sql tables. Now link to them in the mdb if you wish, and it will all be 'in' the mdb....for appearances sake anyway

 

[AccessBlaster]

I like to keep together in one database as in mdb .. how to get there

They are together it's just not the view you are used to seeing. If you download SQL Server Management Studio (SSMS) you will see the tables are in a container-like view. In the sample image below if you expaned the Northwind DB you would see the related tables.

HTH

 

[FuzMic]

@A.Blaster .. Container is what I want, where do I start to import to get there. I think I choose the wrong way to import that ends up me not seeing the Container. Your response DO help to clarify my question

 

[AccessBlaster]

Have a look here for a good tutorial

 

[GPGeorge]

@A.Blaster .. Container is what I want, where do I start to import to get there. I think I choose the wrong way to import that ends up me not seeing the Container. Your response DO help to clarify my question

It's entirely possible that you actually imported the tables into one of the system databases on the Server, rather than into a new database created for this project. It wouldn't be the first time (ahem) that "someone" did that.

 

[FuzMic]

i downloaded the migration assistant for access but it is for Access 8.23. I only wish to use sql server 2008 however the downloaded assistant does not point to SqlServer 2008 R2.

Friends i will like to try to work on 3 alternative
1 where can i get a download Migration Assistant that feed to SQLserver 2008, some download link are broken.
2 is there any other way to migrate eg using the SSMStudio or use import.export option (this option did not create the container)
3 I have the -ADV.exe, do you think that it have the appropriate Migration Assistant?

 

[GPGeorge]

i downloaded the migration assistant for access but it is for Access 8.23. I only wish to use sql server 2008 however the downloaded assistant does not point to SqlServer 2008 R2.

Friends i will like to try to work on 3 alternative
1 where can i get a download Migration Assistant that feed to SQLserver 2008, some download link are broken.
2 is there any other way to migrate eg using the SSMStudio or use import.export option (this option did not create the container)
3 I have the -ADV.exe, do you think that it have the appropriate Migration Assistant?

No, SSMA 8.23 only means that it is the 8.23 version of SSMA itself. That's nothing to do with Access versions, only the SSMA app.

It may well be that they no longer support SQL Server 2008 R2. It's been long out of support, IIRC. Perhaps, though, you can try your luck with the lowest version SSMA does support, probably 2012. See if that can work anyway.

You guys are confusing with me references to "containers". What does that mean in the context of a SQL Server instance? SQL Azure supports containers as a way to deploy instances of SQL Server, but that's at a different, and higher, level than databases within a SQL Server instances or tables within a SQL Server database.

What is the -ADV.exe supposed to be? That's another new term for me.

 

[AccessBlaster]

You guys are confusing with me references to "containers".

There are no containers, it's the OP's understanding of where his tables reside within SQL Server.

 

[GPGeorge]

There are no containers, it's the OP's understanding of where his tables reside within SQL Server.

I see, well, I think it's wiser to use terms that match the facts. "Containers" might work as an analogy, of course, as in "Databases are similar to containers in that the tables are stored inside databases". However, the actual term is "database" and that's what any documentation will refer to. In this case, it's possible that the tables may have gone into a different database, as I suggested above.

 

[FuzMic]

I don't need to use migrate assistant for access, just use SSMS create the intended database name & then import tables from access mdb into it.

 

[Minty]

If you have created the SQL database already then you can right-click on the table in Access and select export, and select ODBC database, and it will create it for you if you have the connection available.