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 Access Database To Sql Server

Hi folks

I want to move Access database to Sql server but I don't want to use Import/Export Wizard. I want to use DTC Designer.

When I tried to do it I can move only one table between Access and Sql db for every connection.

So, what should I do?

I want to move some tables e.g. ten tables to temp db and then I want to put P.K and Uni. constraints on the main tables then move them and the rest of the tables to the currect destination database.

Did DTC Designer help me on this or should I use Import/Export Wizard.

Thanks guysI would use the Import Wizard or the appropiate Upsizing wizard in Access if applicable for your version of Access.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Thanks for reply

The issue here that Access database contain data not correct. Due to

wrong data, I have to repair them before moving

it. Therefore, I have to design a temporary database and then set the conditions

on the database. The issue here I want to build DTS that move strong entity and

check the data and the conditions. When an error occurs, I want DTS to primate a

message to correct the data manually.

Can you give me any suggestion?

Thank you

|||Thank all for answring

I Moved data by using standard SQL statment

Select , Insert .....

Thnaks

Bye

Moving Access Database To Sql Server

Hi folks

I want to move Access database to Sql server but I don't want to use Import/Export Wizard. I want to use DTC Designer.

When I tried to do it I can move only one table between Access and Sql db for every connection.

So, what should I do?

I want to move some tables e.g. ten tables to temp db and then I want to put P.K and Uni. constraints on the main tables then move them and the rest of the tables to the currect destination database.

Did DTC Designer help me on this or should I use Import/Export Wizard.

Thanks guysI would use the Import Wizard or the appropiate Upsizing wizard in Access if applicable for your version of Access.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Thanks for reply

The issue here that Access database contain data not correct. Due to

wrong data, I have to repair them before moving

it. Therefore, I have to design a temporary database and then set the conditions

on the database. The issue here I want to build DTS that move strong entity and

check the data and the conditions. When an error occurs, I want DTS to primate a

message to correct the data manually.

Can you give me any suggestion?

Thank you

|||Thank all for answring

I Moved data by using standard SQL statment

Select , Insert .....

Thnaks

Bye

Moving a SQL7 DB to SQL2000

I have a backup of a SQL7 DB that i need to restore into SQL 2000. I only have the backup of the DB , i don't have access to the SQL7 server. When ever I try to restore the DB into SQL2000 nothing works, the tables and properties all restore properly and the data is there but i can't retrieve info from it. I'm trying to move a website DB from SQL7 to SQL 2000 for use with Cold Fusion. I know they're is issues with going from 7 to 2000 but i don't know what they are. If anyone could help me with this i would greatly appreciate it. I know I need to do some special steps or changes before it will work in 2000, does anyone know what these are?

Thanks in advance,
B"...I can't retrieve info from it..."

can you provide a few more details? What are you using to retrieve info (QA, development IDE, EM)? What error messages are you getting?

Recall that if you restore a database onto a new server, the logins and permissions don't convey (kind of like the furniture in my house when I go to sell it).

Permissions may be a part of your issue.

Regards,

hmscott|||I've got it working, I had to do a DTS Import and I checked Use Collation. This seemed to fix the problem, thanks to those who helped!!!

Moving a SQL DB

Sorry for the remedial question...Newbie here...
I need to move a sql db from one sql machine to another, i do have access to
MMC...Can someone please provide me with detailed instructions?
I have experimented with exporting the data from one and importing to
another, but i haven't had much luck, primary keys seem to get messed up.
Any help would be greatly appreciated.
THanks!Hi, eye,
Simple method. Backup the database from one server, restore it to the
other.
You may wind up with some orphaned users. Look at topics in the BOL such
as:
Troubleshooting Orphaned Users
http://tinyurl.com/7yjwt
Also, the commands sp_validatelogins, sp_change_users_login,
sp_resolve_logins may be helpful for straightening things out.
Russell Fields
"eyespike1" <eyespike1@.discussions.microsoft.com> wrote in message
news:D4CE5D5F-798A-4645-BB29-7D85CBCF94A0@.microsoft.com...
> Sorry for the remedial question...Newbie here...
> I need to move a sql db from one sql machine to another, i do have access
to
> MMC...Can someone please provide me with detailed instructions?
> I have experimented with exporting the data from one and importing to
> another, but i haven't had much luck, primary keys seem to get messed up.
> Any help would be greatly appreciated.
> THanks!
>|||Use BACKUP and RESTORE commands.
For example:
--On the source server
BACKUP DATABASE YourDatabase TO
DISK='G:\MSSQL\Backup\YourDatabaseBackup
.BAK'
--Copy the backup file or share it, so that it is visible on the target
server
--Run the following command to restore it
RESTORE DATABASE YourDatabase FROM
DISK='G:\MSSQL\Backup\YourDatabaseBackup
.BAK'
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"eyespike1" <eyespike1@.discussions.microsoft.com> wrote in message
news:D4CE5D5F-798A-4645-BB29-7D85CBCF94A0@.microsoft.com...
Sorry for the remedial question...Newbie here...
I need to move a sql db from one sql machine to another, i do have access to
MMC...Can someone please provide me with detailed instructions?
I have experimented with exporting the data from one and importing to
another, but i haven't had much luck, primary keys seem to get messed up.
Any help would be greatly appreciated.
THanks!

Moving a SQL DB

Sorry for the remedial question...Newbie here...
I need to move a sql db from one sql machine to another, i do have access to
MMC...Can someone please provide me with detailed instructions?
I have experimented with exporting the data from one and importing to
another, but i haven't had much luck, primary keys seem to get messed up.
Any help would be greatly appreciated.
THanks!
Hi, eye,
Simple method. Backup the database from one server, restore it to the
other.
You may wind up with some orphaned users. Look at topics in the BOL such
as:
Troubleshooting Orphaned Users
http://tinyurl.com/7yjwt
Also, the commands sp_validatelogins, sp_change_users_login,
sp_resolve_logins may be helpful for straightening things out.
Russell Fields
"eyespike1" <eyespike1@.discussions.microsoft.com> wrote in message
news:D4CE5D5F-798A-4645-BB29-7D85CBCF94A0@.microsoft.com...
> Sorry for the remedial question...Newbie here...
> I need to move a sql db from one sql machine to another, i do have access
to
> MMC...Can someone please provide me with detailed instructions?
> I have experimented with exporting the data from one and importing to
> another, but i haven't had much luck, primary keys seem to get messed up.
> Any help would be greatly appreciated.
> THanks!
>
|||Use BACKUP and RESTORE commands.
For example:
--On the source server
BACKUP DATABASE YourDatabase TO
DISK='G:\MSSQL\Backup\YourDatabaseBackup.BAK'
--Copy the backup file or share it, so that it is visible on the target
server
--Run the following command to restore it
RESTORE DATABASE YourDatabase FROM
DISK='G:\MSSQL\Backup\YourDatabaseBackup.BAK'
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"eyespike1" <eyespike1@.discussions.microsoft.com> wrote in message
news:D4CE5D5F-798A-4645-BB29-7D85CBCF94A0@.microsoft.com...
Sorry for the remedial question...Newbie here...
I need to move a sql db from one sql machine to another, i do have access to
MMC...Can someone please provide me with detailed instructions?
I have experimented with exporting the data from one and importing to
another, but i haven't had much luck, primary keys seem to get messed up.
Any help would be greatly appreciated.
THanks!

moving a heap to another filegroup

I have a big table (heap)... well, not so big, I have a small server
and I want to spread access to it across several new disks dedicated
only to that table.

I known its possible to do that creating a clustered index with "ON
filegroup" option but I want to maintain it as a heap, is there any
way to do this without dropping indexes/references - bulk unload -
create table - bulk load - create indexes?."el emperador" <1492a2001@.terra.es> wrote in message
news:dc979468.0407291227.11f42272@.posting.google.c om...
> I have a big table (heap)... well, not so big, I have a small server
> and I want to spread access to it across several new disks dedicated
> only to that table.
> I known its possible to do that creating a clustered index with "ON
> filegroup" option but I want to maintain it as a heap, is there any
> way to do this without dropping indexes/references - bulk unload -
> create table - bulk load - create indexes?.

As far as I know, you have to drop everything and recreate it, as you have
described. But in general, in MSSQL it's a good idea to have a clustered
index on all tables, so it would be interesting to know why you prefer to
maintain a heap table.

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message news:<410999b5$1_3@.news.bluewin.ch>...
> "el emperador" <1492a2001@.terra.es> wrote in message
> news:dc979468.0407291227.11f42272@.posting.google.c om...
> > I have a big table (heap)... well, not so big, I have a small server
> > and I want to spread access to it across several new disks dedicated
> > only to that table.
> > I known its possible to do that creating a clustered index with "ON
> > filegroup" option but I want to maintain it as a heap, is there any
> > way to do this without dropping indexes/references - bulk unload -
> > create table - bulk load - create indexes?.
> As far as I know, you have to drop everything and recreate it, as you have
> described. But in general, in MSSQL it's a good idea to have a clustered
> index on all tables, so it would be interesting to know why you prefer to

I agree.

> maintain a heap table.

Well, I have found that after define a clustered index some queries
changed their plan (from a "Index Seek"-->"Nested Loops"-->"Bookmark
Lookup" to a "Clustered Index Seek") and surprisingly got a little
slower, that's suggar for the query optimizer so it thinks its better
to use it, I have to investigate why this happens before define "the
correct" clustered index, a not easy task as you know. Right now I
only want to improve "bookmark lookup" on this table that is 60%-80%
of the weigth of the queries.

> Simon

Thanks|||Hi

Adding the clustered index in the new filegroup and then dropping it will
leave the data in the new filegroup. Your other indexes will be rebuilt
twice though, so it may be quicker to drop and re-create them yourself.

John

"el emperador" <1492a2001@.terra.es> wrote in message
news:dc979468.0407301415.28c2c4@.posting.google.com ...
> "Simon Hayes" <sql@.hayes.ch> wrote in message
news:<410999b5$1_3@.news.bluewin.ch>...
> > "el emperador" <1492a2001@.terra.es> wrote in message
> > news:dc979468.0407291227.11f42272@.posting.google.c om...
> > > I have a big table (heap)... well, not so big, I have a small server
> > > and I want to spread access to it across several new disks dedicated
> > > only to that table.
> > > > I known its possible to do that creating a clustered index with "ON
> > > filegroup" option but I want to maintain it as a heap, is there any
> > > way to do this without dropping indexes/references - bulk unload -
> > > create table - bulk load - create indexes?.
> > As far as I know, you have to drop everything and recreate it, as you
have
> > described. But in general, in MSSQL it's a good idea to have a clustered
> > index on all tables, so it would be interesting to know why you prefer
to
> I agree.
> > maintain a heap table.
> Well, I have found that after define a clustered index some queries
> changed their plan (from a "Index Seek"-->"Nested Loops"-->"Bookmark
> Lookup" to a "Clustered Index Seek") and surprisingly got a little
> slower, that's suggar for the query optimizer so it thinks its better
> to use it, I have to investigate why this happens before define "the
> correct" clustered index, a not easy task as you know. Right now I
> only want to improve "bookmark lookup" on this table that is 60%-80%
> of the weigth of the queries.
> > Simon
> Thanks|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<dBPOc.12029$vm4.122553507@.news-text.cableinet.net>...
> Hi
> Adding the clustered index in the new filegroup and then dropping it will
> leave the data in the new filegroup. Your other indexes will be rebuilt
> twice though, so it may be quicker to drop and re-create them yourself.
> John

Thanks John, that's perfect.

Moving a database SPID 51 error access error

Hi,
When I follow the instructions found here
http://support.microsoft.com/?id=224071 to move a (user) database I get an
error. This relates to unable to get exclusive access - there is only on
lock (spid 51) - can I just kill this?
sql 2000 - sp3 (so i am told) window 2000 sp4
Thanks
LeeOk, no worries - it was enterprise manager - rookie mistake
"Lee J" <ljackson_js@.hotmailREMOVESPAMMY.com> wrote in message
news:e9m0bs$7s1@.dispatch.concentric.net...
> Hi,
> When I follow the instructions found here
> http://support.microsoft.com/?id=224071 to move a (user) database I get an
> error. This relates to unable to get exclusive access - there is only on
> lock (spid 51) - can I just kill this?
> sql 2000 - sp3 (so i am told) window 2000 sp4
> Thanks
> Lee
>

Moving a database SPID 51 error access error

Hi,
When I follow the instructions found here
http://support.microsoft.com/?id=224071 to move a (user) database I get an
error. This relates to unable to get exclusive access - there is only on
lock (spid 51) - can I just kill this?
sql 2000 - sp3 (so i am told) window 2000 sp4
Thanks
LeeOk, no worries - it was enterprise manager - rookie mistake
"Lee J" <ljackson_js@.hotmailREMOVESPAMMY.com> wrote in message
news:e9m0bs$7s1@.dispatch.concentric.net...
> Hi,
> When I follow the instructions found here
> http://support.microsoft.com/?id=224071 to move a (user) database I get an
> error. This relates to unable to get exclusive access - there is only on
> lock (spid 51) - can I just kill this?
> sql 2000 - sp3 (so i am told) window 2000 sp4
> Thanks
> Lee
>

Moving : AccessSQL Server 2005(Enterprise Edition) VB6—VB.Net

I am a new in .Net Environment. I am moving from VB to VB.Net and Access DB to SQL Server 2005. Please reply me the following questions bellow.

Access Works

SQL SERVER (SS)?

When I create .MSI file it include ADO library in that executable file and my client install software and don’t need any kind of file to install and wherever my program install it can be accessed by using following connection string.

Con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\db\PAYROLL_DB.mdb;Persist Security Info=False" Con.Open

What file needed on client’s PC to access SS on server.

What about connection string change dynamically in client sides

I create relation on Access Relationship Diagram.

Where to create these diagrams either on VB.Net Server Explorer or on Management Studio? And how?

Please answers me this basic questions further I have more question in mind but please first answers me these questions…

Hi,

What file needed on client’s PC to access SS on server.

What about connection string change dynamically in client sides

-You will just need a driver to access SQL Server like the ADO (included in MDAC) or ADO.NET (included in the .NET FW) or the SQL Server Native Client (SNAC).

Where to create these diagrams either on VB.Net Server Explorer or on Management Studio? And how?

I would create them in the SSMS, don′t know if the functionality is EXACTLY the same as in server explorer, but I am used to to it there, but from a short inspection of mine I would say that they are feature-identical.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Moved asp.net app to network share, cant access SQL

I've recently moved an asp.net website from my PC to a network share because another tech it going to be working on it. I finally got the correct permissions on the network share and the correct .NET Framework settings on my PC to be able to run the app. Now I can't access the SQL server which is on a different server. Getting the following error:

Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

How to I setup access to my SQL server for the app from any given PC on my LAN?

Your current error is not SQL Server permissions but CAS(code access security) you have to fix that before, you can get the error related to SQL Server permissions if any. The two links below covers the issues related to your error. Hope this helps.

http://forums.asp.net/thread/1522588.aspx

http://blogs.msdn.com/shawnfa/archive/2003/06/20/57023.aspx

Move sql 2005 ee db to new server without disk access?

Is there any reasonably simple way to install the mdf and ldf files for a sq
l
server 2005 ee database on a server where one does not have access to the da
ta
folder of the sql server? IE using the ee management studio from a workstati
on?On Feb 12, 12:19 pm, "ms" <m...@.ms.ms> wrote:[vbcol=seagreen]
> Is there any reasonably simple way to install the mdf and ldf files for a
sql
> server 2005 ee database on a server where one does not have access to the
data
> folder of the sql server? IE using the ee management studio from a workstation?[/v
bcol]
Does the SQL Server service account have access to the new location?
You could try using xp_cmdshell to execute OS commands to copy the mdf/
ldf files. Detach the DB, copy with xp_cmdshell, the reattach.|||On 12/02/2007 "Tracy McKibben" <tracy.mckibben@.gmail.com> wrote:
>On Feb 12, 12:19 pm, "ms" <m...@.ms.ms> wrote:
data[vbcol=seagreen]
workstation?[vbcol=seagreen]
>Does the SQL Server service account have access to the new location?
>You could try using xp_cmdshell to execute OS commands to copy the mdf/
>ldf files. Detach the DB, copy with xp_cmdshell, the reattach.
>
Thanks Tracey, that might be possible, I'll check.

Move sql 2005 ee db to new server without disk access?

Is there any reasonably simple way to install the mdf and ldf files for a sql
server 2005 ee database on a server where one does not have access to the data
folder of the sql server? IE using the ee management studio from a workstation?
On Feb 12, 12:19 pm, "ms" <m...@.ms.ms> wrote:
> Is there any reasonably simple way to install the mdf and ldf files for a sql
> server 2005 ee database on a server where one does not have access to the data
> folder of the sql server? IE using the ee management studio from a workstation?
Does the SQL Server service account have access to the new location?
You could try using xp_cmdshell to execute OS commands to copy the mdf/
ldf files. Detach the DB, copy with xp_cmdshell, the reattach.
|||On 12/02/2007 "Tracy McKibben" <tracy.mckibben@.gmail.com> wrote:[vbcol=seagreen]
>On Feb 12, 12:19 pm, "ms" <m...@.ms.ms> wrote:
data[vbcol=seagreen]
workstation?
>Does the SQL Server service account have access to the new location?
>You could try using xp_cmdshell to execute OS commands to copy the mdf/
>ldf files. Detach the DB, copy with xp_cmdshell, the reattach.
>
Thanks Tracey, that might be possible, I'll check.

Move sql 2005 ee db to new server without disk access?

Is there any reasonably simple way to install the mdf and ldf files for a sql
server 2005 ee database on a server where one does not have access to the data
folder of the sql server? IE using the ee management studio from a workstation?On Feb 12, 12:19 pm, "ms" <m...@.ms.ms> wrote:
> Is there any reasonably simple way to install the mdf and ldf files for a sql
> server 2005 ee database on a server where one does not have access to the data
> folder of the sql server? IE using the ee management studio from a workstation?
Does the SQL Server service account have access to the new location?
You could try using xp_cmdshell to execute OS commands to copy the mdf/
ldf files. Detach the DB, copy with xp_cmdshell, the reattach.|||On 12/02/2007 "Tracy McKibben" <tracy.mckibben@.gmail.com> wrote:
>On Feb 12, 12:19 pm, "ms" <m...@.ms.ms> wrote:
>> Is there any reasonably simple way to install the mdf and ldf files for a sql
>> server 2005 ee database on a server where one does not have access to the
data
>> folder of the sql server? IE using the ee management studio from a
workstation?
>Does the SQL Server service account have access to the new location?
>You could try using xp_cmdshell to execute OS commands to copy the mdf/
>ldf files. Detach the DB, copy with xp_cmdshell, the reattach.
>
Thanks Tracey, that might be possible, I'll check.