Re: Copying from one record set to another
- From: INTP56 <INTP56@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 18 Jun 2008 19:46:01 -0700
Bob,
I had a version using linked servers in SQL Server, but it stopped working.
I have another thread in the SQL server newsgroup looking for an answer
there. I've made an SSIS package that does the job, but I can't call the
package from the client side unless I give the user permission to run
sp_startjob. I've made another version using the service broker, but again, I
seem to have to give users special permissions to make it work.
My client (and I) wants to load data immediately without my intervention. As
I said in my initial post, I am trying to do a workaround for my client until
I can get the SQL Server implementation that does the same thing working so
they can continue to upload data without my intervention. My thought was I
could quickly make an Excel workbook to do what was necessary for now.
Any suggestions on the problem I asked about?
Bob
"Bob Barrows [MVP]" wrote:
INTP56 wrote:.
Hi, I am trying to do a workaround while I'm trying to resolve an
issue with the SQL Server implementation that does the same thing,
but is currently broken.
Basically, I have an MDB file with two tables in it, and two tables
in SQL Server that look just like the tables in the access database
Both tables have an auto increment field on the Access side. I want to
select the new records from the Access db and insert them into the
SQL DB.
I can get to both DBs from Excel. I can write code that pulls the new
records from Access into Excel,
Excel???
Stop. You're killing me :-)
There are some simple solutions for this situation:
1. In Access, simply create links to the two sql server tables in your
Access mdb and run a couple insert queries.
2. In SQL Server: create a linked server in SQL Server pointing at the
Access database and do the same thing: run a couple insert queries to
copy the data.
3. In SQL Server: use DTS (SSIS if SQL2005) to import the data from
Access into SQL Server?
OK, OK, I know the answer is that you probably don't know anything about
these concepts. Well, now's the time to start reading:
read about linked tables in Access online help - a linked table can be
treated just like a local Access table in a query
or
linked servers in SQL Server BOL (online help)
or
DTS/SSIS in SQL Server BOL (online help)
If you need help with option 1, post to an Access newsgroup.
For help with the other two options, post to a sql server newsgroup
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
- Follow-Ups:
- Re: Copying from one record set to another
- From: Bob Barrows [MVP]
- Re: Copying from one record set to another
- References:
- Copying from one record set to another
- From: INTP56
- Re: Copying from one record set to another
- From: Bob Barrows [MVP]
- Copying from one record set to another
- Prev by Date: Re: Copying from one record set to another
- Next by Date: Re: Copying from one record set to another
- Previous by thread: Re: Copying from one record set to another
- Next by thread: Re: Copying from one record set to another
- Index(es):
Relevant Pages
|
