Moving an Access Database to SQL Server.

Is there a way of taking an access database and create a replica of that
database as a sql Server database, but only doing it by code. Reason being I
am asking this is because we currently have clients that are running on an
access database using the access runtime and I am installing the Microsoft
Sql Server Desktop Engine (MSDE) and wish to transfer their existing access
database to the SQL Server Environment. I would like the user to do this
function through my program, and also make it easy for them to use.search on comp.databases.ms-access for this I'm sure you'll find what
you need.|||It is straightforward to build a DTS package that imports all MS Access
tables to SQL Server. One option is to open the package in the designer and
then Package.. Save As.. the package as a Visual Basic project which can be
compiled as an executable for distribution to the client.
http://msdn.microsoft.com/library/d...>
wiz_9e5g.asp
http://msdn.microsoft.com/library/d...br />
8jfp.asp
However, keep in mind that some queries designed for MS Access may not be
compatable with SQL Server.
http://www.microsoft.com/technet/pr...rt11/sqc18.mspx
"Db_Stuff" <DbStuff@.discussions.microsoft.com> wrote in message
news:A59D96E3-BCBE-44C0-97CA-E94D0A3AF435@.microsoft.com...
> Is there a way of taking an access database and create a replica of that
> database as a sql Server database, but only doing it by code. Reason being
> I
> am asking this is because we currently have clients that are running on an
> access database using the access runtime and I am installing the Microsoft
> Sql Server Desktop Engine (MSDE) and wish to transfer their existing
> access
> database to the SQL Server Environment. I would like the user to do this
> function through my program, and also make it easy for them to use.|||>> I am installing the Microsoft Sql Server Desktop Engine (MSDE)
I'm not sure if he has access to DTS, it's not part of MSDE.
And Access itself has upsizing functionality|||Creating the DTS package would require SQL Server developer edition or
higher, but it can be run against MSDE.
"Gerard" <g.doeswijk@.gmail.com> wrote in message
news:1134052256.228445.111720@.o13g2000cwo.googlegroups.com...
> I'm not sure if he has access to DTS, it's not part of MSDE.
> And Access itself has upsizing functionality
>|||The Access Upsizing Wizard accessible from the tools menu should transfer
Access data to SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"JT" <someone@.microsoft.com> wrote in message
news:Ox2FVuA$FHA.3392@.TK2MSFTNGP14.phx.gbl...
> Creating the DTS package would require SQL Server developer edition or
> higher, but it can be run against MSDE.
> "Gerard" <g.doeswijk@.gmail.com> wrote in message
> news:1134052256.228445.111720@.o13g2000cwo.googlegroups.com...
>|||Will the Upsizing Wizard translate (if needed) the SQL queries. It sounds
like perhaps the OP has an Access application (or at least an application
that has been using Access) and he wants to upsize the backend database to
SQL Server.
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:O9UKt2A$FHA.356@.TK2MSFTNGP12.phx.gbl...
> The Access Upsizing Wizard accessible from the tools menu should transfer
> Access data to SQL Server
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "JT" <someone@.microsoft.com> wrote in message
> news:Ox2FVuA$FHA.3392@.TK2MSFTNGP14.phx.gbl...
>|||No, the upsizing wizard just moves the data you may or may no have to change
queries depending on your application. In general, SQL Server is enough
different than Jet that some queries will have to be rewritten to get decent
performance.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"JT" <someone@.microsoft.com> wrote in message
news:O2dbCQB$FHA.2420@.TK2MSFTNGP12.phx.gbl...
> Will the Upsizing Wizard translate (if needed) the SQL queries. It sounds
> like perhaps the OP has an Access application (or at least an application
> that has been using Access) and he wants to upsize the backend database to
> SQL Server.
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:O9UKt2A$FHA.356@.TK2MSFTNGP12.phx.gbl...
>

Moving a database application to SQL Server 2005 Express

I have been programming an application with VC++ 2005 and SQL Server 2005. I have converted an old 16-bit database to 32-bit managed code and SQL server and the application seems to be good. Now I want to deploy the application to another server for testing.

I have installed XP SP2, Windows Installer 3.1, Net framework 2.0 and SQL Server 2005 express to the test server. I have transferred the application with WI 3.1 and the program works well in the test server till the first SQL command. I have made a back up of the database and restored it in the test server. In the test server I can log in the database with Server Management studio and I can read the data there correctly. I have enabled both named pipes and TCP/IP for the database in the test server. With Surface Area Configuration I have enabled Local and Remote Connections Using both TCP/IP and named pipes. I only need Windows authentication at this time.

After all this when I come to the first SQL command in the application on the test server I receive the error message:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server).

My connection string to the database is:
'connection->ConnectionString = "Persist Security Info=False; Integrated Security =SSPI;"
"Data Source=TESTSERVER; Initial Catalog=TESTDATABASE;";'

When I use "Data Source=DevelopmentServer", the application works well on the development server.

Can't understand what is still wrong. Can you possibly have an answer for me?

hi,

are you connecting locally or from remote clients?

check with the SQL Server Configuration Manager the appropriate network protocols are enabled as well..

regards

|||

Thank you for your answer!

Actually I am connecting to the TestServer only locally. I enabled TCP/IP and named pipes only because of the error message (40) and I enabled those features using SQL Server Configuration Manager as you suggest. This is done but I still receive the error.

|||

Finally, the solution found!

When connecting to the TestServer the connection is not actually to the server but the instance. The connection string must have format TestServer\SQLExpress. I had this and the compiler could compile and link the code without errors, but there was a warning of wrong character sequence. When I have to repeat the connection command repeatedly, I had defined the string with

#define COMMANDSTRING "Data Source=TestServer\SQLExpress"

The correct format is

#define COMMANDSTRING "Data Source=TestServer\\SQLExpress"

although the backslash is within quotation marks.