Access to SQL replication

I have a customer who has Access databases at many sites across the country
who wants the
data replicated nightly to a centralized MS-SQL Server using broadband
connection (Cable, DSL, etc.)
I have very limited knowledge of best practices to accomplish this reliably
but know their current solution is not at all reliable. Right now they are
using some Java app that exports the Access data to CSV, then emails the
files. The emails are then manually taken, copied then imported. Very
cumbersome process and VERY unreliable. There's gotta be a better way!
I want to recomend that they contract an expert to set this up for them and
then they can maintain it. However because this isn't my area, I can't
really
say for certian what type of person they should look for or how it should be
done.
Any ideas from a 40,000 foot perspective?
Thanks in advance.
you can use merge replication for this. Each access database would enabled
as a subscriber to a single merge publication on your host SQL Server.
You will have to do some partitioning or filtering to ensure that there are
no conflicts.
"Ben" <Sorry@.TooManyViruses.com> wrote in message
news:dSvic.37377$lS2.27451@.twister.rdc-kc.rr.com...
> I have a customer who has Access databases at many sites across the
country
> who wants the
> data replicated nightly to a centralized MS-SQL Server using broadband
> connection (Cable, DSL, etc.)
> I have very limited knowledge of best practices to accomplish this
reliably
> but know their current solution is not at all reliable. Right now they are
> using some Java app that exports the Access data to CSV, then emails the
> files. The emails are then manually taken, copied then imported. Very
> cumbersome process and VERY unreliable. There's gotta be a better way!
> I want to recomend that they contract an expert to set this up for them
and
> then they can maintain it. However because this isn't my area, I can't
> really
> say for certian what type of person they should look for or how it should
be
> done.
> Any ideas from a 40,000 foot perspective?
> Thanks in advance.
>

Access Sql server 2005 from .net class library

I have Sql server 2005 running on a workgroup server and wish to access the
data from another workgroup server using direct sql access (ie not ODBC). Th
e
code to access the data is written in visual studio 2005 and is within a
class library DLL. I wish to use Windows Authentication between the two
servers, but am not clear how to set the user within the code. Do I need to
use Component Services (ie MTS) to do this?Just use regular connection string for Windows Authentication, you can find
the syntax at www.connectionstrings.com. Since you are in a workgroup, you
may have to use a mirrored account (same username/password on both
computers).
Roman
Roman Rehak
http://sqlblog.com/blogs/roman_rehak
"lankylad" <lankylad@.discussions.microsoft.com> wrote in message
news:F4F155F9-1D2E-449F-A5FF-5FCD24CF1025@.microsoft.com...
>I have Sql server 2005 running on a workgroup server and wish to access the
> data from another workgroup server using direct sql access (ie not ODBC).
> The
> code to access the data is written in visual studio 2005 and is within a
> class library DLL. I wish to use Windows Authentication between the two
> servers, but am not clear how to set the user within the code. Do I need
> to
> use Component Services (ie MTS) to do this?|||No need for COM+/MTS. The important thing here is that the class library
has the capability to accept connection string parameters and switch them
when necessary. Refer to the Enterprise Application Block, particularly the
Dta Access Application Block for samples. As Roman mentioned, since you are
in a workgroup environment, mixed mode authentication is a bit simpler for
this - create the same account on both server and assign permissions for the
necessary securables
"Roman Rehak" <rrehak@.hotmail.com> wrote in message
news:%23peB3KqwHHA.4628@.TK2MSFTNGP02.phx.gbl...
> Just use regular connection string for Windows Authentication, you can
> find the syntax at www.connectionstrings.com. Since you are in a
> workgroup, you may have to use a mirrored account (same username/password
> on both computers).
> Roman
> --
> Roman Rehak
> http://sqlblog.com/blogs/roman_rehak
>
> "lankylad" <lankylad@.discussions.microsoft.com> wrote in message
> news:F4F155F9-1D2E-449F-A5FF-5FCD24CF1025@.microsoft.com...
>|||Thanks both.
www.connectionstrings.com shows that the format for a Trusted Connection in
SQLConnection (.NET) is:
Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated
Security=SSPI;
Do I just add a "User Id" entry to the end of that?
"bass_player [SBS-MVP]" wrote:

> No need for COM+/MTS. The important thing here is that the class library
> has the capability to accept connection string parameters and switch them
> when necessary. Refer to the Enterprise Application Block, particularly th
e
> Dta Access Application Block for samples. As Roman mentioned, since you a
re
> in a workgroup environment, mixed mode authentication is a bit simpler for
> this - create the same account on both server and assign permissions for t
he
> necessary securables
>
> "Roman Rehak" <rrehak@.hotmail.com> wrote in message
> news:%23peB3KqwHHA.4628@.TK2MSFTNGP02.phx.gbl...
>
>|||lankylad (lankylad@.discussions.microsoft.com) writes:
> www.connectionstrings.com shows that the format for a Trusted Connection
> in SQLConnection (.NET) is:
> Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated
> Security=SSPI;
> Do I just add a "User Id" entry to the end of that?
No. User Id is for SQL Authentication. When you use Windows Authentication,
the presumption is that you are already logged into Windows. And if you
are logged in as MACHINE1\USER1, you cannot log into SQL Server as
MACHINE2\USER2, you can only connect to SQL Server with the Windows
user you are logged in as.
Note that to get Windows Authentication to work in a workgroup, you
need to take some precautions. First, the usernamd and password must be
the same on the two machines. Next, run gpedit.msc, and check
Computer Cnofiguration->Windows Settings->Security Settings->
Local Policies->Security Options->Network access: Sharing and Security
model for local accounts. This needs to be set to Classic for the scheme
to work.
Given all this trickery, SQL authentication may be a better option in a
workgroup.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks.
Since I'm writing the data access code in a Class Library DLL does that when
I'm using the class from a web application I have to use Anonymous login and
have that user set up on my sqlserver server with the same password?
"Erland Sommarskog" wrote:

> lankylad (lankylad@.discussions.microsoft.com) writes:
> No. User Id is for SQL Authentication. When you use Windows Authentication
,
> the presumption is that you are already logged into Windows. And if you
> are logged in as MACHINE1\USER1, you cannot log into SQL Server as
> MACHINE2\USER2, you can only connect to SQL Server with the Windows
> user you are logged in as.
> Note that to get Windows Authentication to work in a workgroup, you
> need to take some precautions. First, the usernamd and password must be
> the same on the two machines. Next, run gpedit.msc, and check
> Computer Cnofiguration->Windows Settings->Security Settings->
> Local Policies->Security Options->Network access: Sharing and Security
> model for local accounts. This needs to be set to Classic for the scheme
> to work.
> Given all this trickery, SQL authentication may be a better option in a
> workgroup.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||lankylad (lankylad@.discussions.microsoft.com) writes:
> Since I'm writing the data access code in a Class Library DLL does that
> when I'm using the class from a web application I have to use Anonymous
> login and have that user set up on my sqlserver server with the same
> password?
Web servers are not my area, but I guess that if you use integrated
security from a web server, then the Windows login under which the
web server runs is what will count. But it may be that an SQL login
is better in this case.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||All the advice from Microsoft seems to be to avoid mixed authentication, so
I
have been trying to use only Windows Authentication.
"Erland Sommarskog" wrote:

> lankylad (lankylad@.discussions.microsoft.com) writes:
> Web servers are not my area, but I guess that if you use integrated
> security from a web server, then the Windows login under which the
> web server runs is what will count. But it may be that an SQL login
> is better in this case.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||lankylad (lankylad@.discussions.microsoft.com) writes:
> All the advice from Microsoft seems to be to avoid mixed authentication,
> so I have been trying to use only Windows Authentication.
I was involved in a thread recently, where people with more experience than
me of ASP .Net appeared to say that SQL Authentication is the way to. See
65" target="_blank">http://groups.google.com/group/micr...48400
65
SQL authentication on SQL 2000 has a couple of problems. The password is
passed only lightly masked, and there is no protection against brute force
attacks. SQL 2005 on Win 2003 is better protected against the latter.
But you should not expose SQL Server on the Internet if possible.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Access import specs to bcp import file

Hello all
We have data that we import using access import specs in to Access. But the
data is getting rather large and we would like to switch to our SQL database
and analyze the data there. Is there any way to use the access specs in SQl
server and import the data using bulk insert or bcp. We are talking about
millions of records here and would like to get a time efficient solutions.
We have utilities that create access import specs but nothing that can
create a sql server import spec or a bcp import spec.
ThanksTake a look at the Bulk Insert task in DTS. There is an
option to generate a format file from the source file.
-Sue
On Thu, 31 Mar 2005 16:15:08 -0500, "Rahul Chatterjee"
<rahul@.benesysinc.com> wrote:

>Hello all
>We have data that we import using access import specs in to Access. But the
>data is getting rather large and we would like to switch to our SQL databas
e
>and analyze the data there. Is there any way to use the access specs in SQl
>server and import the data using bulk insert or bcp. We are talking about
>millions of records here and would like to get a time efficient solutions.
>We have utilities that create access import specs but nothing that can
>create a sql server import spec or a bcp import spec.
>Thanks
>

Access import specs to bcp import file

Hello all
We have data that we import using access import specs in to Access. But the
data is getting rather large and we would like to switch to our SQL database
and analyze the data there. Is there any way to use the access specs in SQl
server and import the data using bulk insert or bcp. We are talking about
millions of records here and would like to get a time efficient solutions.
We have utilities that create access import specs but nothing that can
create a sql server import spec or a bcp import spec.
Thanks
Take a look at the Bulk Insert task in DTS. There is an
option to generate a format file from the source file.
-Sue
On Thu, 31 Mar 2005 16:15:08 -0500, "Rahul Chatterjee"
<rahul@.benesysinc.com> wrote:

>Hello all
>We have data that we import using access import specs in to Access. But the
>data is getting rather large and we would like to switch to our SQL database
>and analyze the data there. Is there any way to use the access specs in SQl
>server and import the data using bulk insert or bcp. We are talking about
>millions of records here and would like to get a time efficient solutions.
>We have utilities that create access import specs but nothing that can
>create a sql server import spec or a bcp import spec.
>Thanks
>