Question Export Database Sql Server 2008 to Access

[AlejandroGG]

Hi

I need to export a database Sql Server 2008 (tables, views and UDFs) to Access Database.

I used this procedure:

-------------------------------------------------------------------------------------
Open sql Server Management Studio and follow the following steps - Right Click on Database GoTo Task -> Export Data - In the choose a source scree - Select source database and provide the credentials - Press Next - In the choose a destination screen - Select Destination as Microsoft Access - Provide a file Path. User id and password if you want to configure - Select option as copy data from one or more table or view and say next - you will see all the tables in database select the respective tables which you want to export and finish It will export the respective tables in the mdb file.-------------------------------------------------------------------------------------

This procedure exported tables and views satisfactorily but not the UDFs.

However, a problem occurs with the bit fields of the tables. If the value of this field is true, it transforms -1 in access.

Also, at the end of the export, the following type out messages in a final report:

The execution was successful
-Initializing Data Flow Task (Success)
-Initializing Connections (Success)
-Setting SQL Command (Success)
-Configuring the Source Connection (Success)
-Configuring destination connection (Success)
-Validating (Success)
-Prepare for implementation (Success)
-Run previously (Success)
-Copying rows (Warning):
Messages:
*Warning: Preparation SQL Task 1: The multi-step operation generated errors OLE DB. Check the status values of OLE DB if possible. We did not perform any work. (Import and Export Wizard in SQL Server)
*Warning: Preparation SQL Task 1: The multi-step operation generated errors OLE DB. Check the status values of OLE DB if possible. We did not perform any work. (Import and Export Wizard in SQL Server)
.....
*Warning: Preparation SQL Task 2: The multi-step operation generated errors OLE DB. Check the status values of OLE DB if possible. We did not perform any work. (Import and Export Wizard in SQL Server)


The lasts warnings involve some type of error in the copying of data?

Anyone can tell me correct procedure for the export? (including UDFs)

Thanks

 

[WayneRyan]

AlejandroGG ,

The conversion between Access/SQL Server is not perfect in either direction.

The SQL Server Bit datatype is brought over as an integer. You'll have to change it if desired.

The SQL Server User-Defined Functions will be of little use in Access. You'll have
to convert these from T-SQL to what Access understands.

Why can't SQL Server host the tables and just use Access as a front-end?

Wayne

 

[AlejandroGG]

WayneRyan, thanks for the reply, i explain my situation.

I am developing an application in VB .NET that will be installed on several machines.

Some of these machines have scarce resources (little cpu, memory, ...).

This means that they'll have to install a lightweight database, as Access, while the more powerful machines, I install sql server 2008.

You say the following:

'Why can't SQL Server host the tables and just use Access as a front-end?'

I do not understand 'just use Access as a front-end'.

Could be useful that in my situation?

What would be the equivalent to udf in access?

Is there more data types that give problems in converting sql server to access?

Thanks

 

[spikepl]

The essential question here is whether each set of data held in the database is to be local to each installation, or shared among all users, or mix thereof ...

For local installations an SQL express might be overkill.

A UDF-equivalent in Access is some VBA function that you can call from SQL. Access SQL does not have the programming logic available in T-SQL. A not fully equivalent solution is to build the SQL dynamically when necessary.

Update:

I do not understand 'just use Access as a front-end'.
Could be useful that in my situation?

Probably not. If you are familar with VB.NET then I do not see the point of dragging Access into it. Here I mean Access as user interface. The second bit of Access - the database engine - is the one that is of interest to you. That is the one that does not require installation of the office application Access.

 

[AlejandroGG]

Less powerful machines are POS terminals, used in restaurants to take orders.

These machines could not point to a remote database because the connection failure would not be allowed.

That means that these machines must have a local database.

Is there more data types that give problems in converting sql server to access?

 

[Steve R.]

That means that these machines must have a local database.

Unless I am missing something, if you can utilize MS Acesss on a local machine, you should be able to to use MS SQL server. I have an MS Access front-end connected to a MySQL back-end on a home network. Both MS Access and MySQL could be on the same computer. Free options could include MS SQL Server Express or MySQL. There are other back-end type type database that can also be used. I would encourage the use of a back-end database that is open source, such as MySQL.

 

[spikepl]

Have you actually verified that the machines with "scarce resources (little cpu, memory, ...)" are unable to run SQL Express ("scarce resources" today is not the same as what it was 2 or 4 years ago)? That would obviate the need to change DB to Access and thus maintain 2 versions of your application. Afterall, a single-user POS-application does not demand much performance.

Regarding data types ? Off-hand hard for me to say- depends on what the original is, I guess. What surprised me in Access is integers (that only go to 34000 or so) . Check what data types your stuff got converted to, and then look up the limitations on each - there are not that many in Access.

 

[AlejandroGG]

When a field is the primary key of a table, is not exported with this attribute.


Neither the relationships between tables, foreign key, ...

How can export this?

 

[Steve R.]

As far as I know, you will have to manually recreate your relationships.

As for the primary key it can be re-linked. In my situation, the (former) primary key was imported as text into a new table (call it Table A) which generated a new primary key as the data was imported. I then created DAO recordsets (of Table A and Table B) which used Find First Record to insert the new primary key as a new foreign key in Table B.

To rephrase this a bit. Use the former primary key in Table A to find a corresponding former foreign key in Table B. Substitute the new primary key from Table A into a new foreign key field in Table B.

After relinking, delete the former primary key field and the former foreign key field.
--------------------------------
PS: More than likely there is SQL solution that would achieve the same objective as using DAO. It would probably be much faster too. However, I have not achieved that SQL skill level yet.

 

[AlejandroGG]

Thanks!

Another problem:

The views are exported as tables.

What is the equivalent of the views in access?

 

[Pat Hartman]

As far as I know, you will have to manually recreate your relationships.

You would just export the constraints as "create" and run them in Access. You'll need to replace the "." with "_" since Access doesn't allow "." in table names.

Queries are the equivalent of views.

Access interprets Boolean data types as true/false and in Jet/ACE, true = -1. That is why 1 is converted to -1. In your queries, always use True rather than 1 or -1 to avoid an issue.

BigInt and the longer date formats are also issues. Since you have to support both back ends, I would change the SQL Server data types to be compatible with Access to avoid issues.

I'm with Spikepl on this one. I would make absolutely certain that the target PCs won't support SQL Server express before I would embark on this conversion.

With an Access app, it is almost trivial to switch between SQL Server (and other RDBMS) and Jet or ACE once you have made certain accommodations. I have several apps that do it. You have to choose compatible data types and in your DAO code, you need to include an extra argument whenever there is an identity column in a table you are using - dbSeeChanges. Aside from that, I can swap BE by deleting all the linked tables and linking to a different BE. I created a form to do this for me since it does require deleting and adding rather than refreshing as you would if you stayed within the same type of BE. SQL Server to SQL Server or ACE to ACE.