Importing multiple Access databases

Hi,
I have 30 remote sites that each day send an access
database of sales figures for that day, how can I automate
the process of importing these into an SQL database. I run
SQL server 2000.
As simple a solution as possible please as I am new to SQL
server.
Thanks Dave
Data Transform Services (DTS) would typically be used to import Access data in to SQL server tables, and packages can be scheduled. There is coverage of DTS in BOL and there are several DTS books (Professional SQL Server 2000 DTS is good).
A way to get started is to use the DTS Import/Export Wizard (see tools/Data Transformation Services) with one of the Access databases as the source and the SQL Server database as the destination. Check the Save Package box (select SQL Server for storage
) to save your package to SQL Server (you might want to uncheck the execute imediate box) and use it as a template.

> Hi,
> I have 30 remote sites that each day send an access
> database of sales figures for that day, how can I automate
> the process of importing these into an SQL database. I run
> SQL server 2000.
> As simple a solution as possible please as I am new to SQL
> server.
> Thanks Dave
>
|||Assuming the access db's always have the same names, and are located in the
same way, you should be able to simply import the data into your SQL
database, by right clicking the database, selecting import, and then going
through the wizard which is reasonably self explanatory.
At the end of the wizard you will be asked if you want to run the import
now, save it, or schedule it. If you choose the schedule option you can then
select a time for that data to be imported, and how often it should be done,
and from there it should go through without a hitch. To test it, go to
Management, SQL Server Agent, Jobs, and you should see your DTS job in the
right hand side. Right click on it and tell it to start, and it will go
through and perform the import. Once finished you can see the status of the
job, and if necessary view the job history and details of what happened if
it fails for some reason.
Hope that helps
Keith
"Dave Tordoff" <david.tordoff@.companioncare.co.uk> wrote in message
news:167d01c46f3f$5c33fbf0$a601280a@.phx.gbl...
> Hi,
> I have 30 remote sites that each day send an access
> database of sales figures for that day, how can I automate
> the process of importing these into an SQL database. I run
> SQL server 2000.
> As simple a solution as possible please as I am new to SQL
> server.
> Thanks Dave

Importing multiple Access databases

Hi,
I have 30 remote sites that each day send an access
database of sales figures for that day, how can I automate
the process of importing these into an SQL database. I run
SQL server 2000.
As simple a solution as possible please as I am new to SQL
server.
Thanks DaveData Transform Services (DTS) would typically be used to import Access data
in to SQL server tables, and packages can be scheduled. There is coverage o
f DTS in BOL and there are several DTS books (Professional SQL Server 2000
DTS is good).
A way to get started is to use the DTS Import/Export Wizard (see tools/Data
Transformation Services) with one of the Access databases as the source and
the SQL Server database as the destination. Check the Save Package box (se
lect SQL Server for storage
) to save your package to SQL Server (you might want to uncheck the execute
imediate box) and use it as a template.

> Hi,
> I have 30 remote sites that each day send an access
> database of sales figures for that day, how can I automate
> the process of importing these into an SQL database. I run
> SQL server 2000.
> As simple a solution as possible please as I am new to SQL
> server.
> Thanks Dave
>|||Assuming the access db's always have the same names, and are located in the
same way, you should be able to simply import the data into your SQL
database, by right clicking the database, selecting import, and then going
through the wizard which is reasonably self explanatory.
At the end of the wizard you will be asked if you want to run the import
now, save it, or schedule it. If you choose the schedule option you can then
select a time for that data to be imported, and how often it should be done,
and from there it should go through without a hitch. To test it, go to
Management, SQL Server Agent, Jobs, and you should see your DTS job in the
right hand side. Right click on it and tell it to start, and it will go
through and perform the import. Once finished you can see the status of the
job, and if necessary view the job history and details of what happened if
it fails for some reason.
Hope that helps
Keith
"Dave Tordoff" <david.tordoff@.companioncare.co.uk> wrote in message
news:167d01c46f3f$5c33fbf0$a601280a@.phx.gbl...
> Hi,
> I have 30 remote sites that each day send an access
> database of sales figures for that day, how can I automate
> the process of importing these into an SQL database. I run
> SQL server 2000.
> As simple a solution as possible please as I am new to SQL
> server.
> Thanks Davesql

Importing from Access question

Hello,
I have a SQL Server database that was created by importing an MS Access
database.
In one of my SQL tables I have added several fields, and have populated some
of these fields with data.
Is there any way to create a DTS package, or do a straight import, where I
pull all records from the original table in Access, but without losing the
data in the new fields in SQL?
Basically I want to rewrite all my SQL data, but retain the new values...
For ex. if I have a record with ID 1000 in both database tables, but in my
SQL I have an extra field, with data...can I import all records from Access,
but for record with ID 1000, retain my new data?
Thanks in advance!
AmberYes, In Access, create a Linked Table (Go File, Get External Data, Link
Tables, and create a linked table that points to the SQL Server Table...
Then in Access, create an Update Query, that updates the Linked Table values
for the columns that are in Access which is based on a join between the
Access table and the SQL Table, on whatever the PK is on both sides..
"amber" wrote:

> Hello,
> I have a SQL Server database that was created by importing an MS Access
> database.
> In one of my SQL tables I have added several fields, and have populated so
me
> of these fields with data.
> Is there any way to create a DTS package, or do a straight import, where I
> pull all records from the original table in Access, but without losing the
> data in the new fields in SQL?
> Basically I want to rewrite all my SQL data, but retain the new values...
> For ex. if I have a record with ID 1000 in both database tables, but in my
> SQL I have an extra field, with data...can I import all records from Acces
s,
> but for record with ID 1000, retain my new data?
> Thanks in advance!
> Amber

importing data from access to sql 2005 nightly

I hope I am giving the right information here to get help with this.
I am trying to get information from two tables that are in an access
database over to sql 2005. I did this via DTS and saved the import options
as a file, but when I run this as a job I get the following error: Date
2/27/2008 11:14:08 AM
Log Job History (CombinationTable1)
Step ID 0
Server D86J0PD1
Job Name CombinationTable1
Step Name (Job outcome)
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
The job failed. The Job was invoked by User BIZCHAIR\DarleneMurphy. The
last step to run was step 1 (CombinationTable1).
I have also tried to run this in the following way:
USE Combination_Table
CREATE TABLE GG Products
AS
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'W:\MASTER DOCUMENTS\Databases\Channel_Advisor_Feed.mdb';'adm in';'', GG
Products)
go
Here I get errors regarding nulls and if I pointed to the correct database,
which I have:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
returned message "'w:\master documents\bizchair-2.mdb' is not a valid path.
Make sure that the path name is spelled correctly and that you are connected
to the server on which the file resides.".
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
Any help with this problem and I hope I have given good information in order
to get help.
Thank you
Dee
Try 'w:\master documents\[bizchair-2].mdb'
Can you use SSIS?
"Dee" <Dee@.discussions.microsoft.com> wrote in message
news:C932BD77-BC9C-4CB8-8E1A-5AB32908C8C1@.microsoft.com...
>I hope I am giving the right information here to get help with this.
> I am trying to get information from two tables that are in an access
> database over to sql 2005. I did this via DTS and saved the import
> options
> as a file, but when I run this as a job I get the following error: Date
> 2/27/2008 11:14:08 AM
> Log Job History (CombinationTable1)
> Step ID 0
> Server D86J0PD1
> Job Name CombinationTable1
> Step Name (Job outcome)
> Duration 00:00:00
> Sql Severity 0
> Sql Message ID 0
> Operator Emailed
> Operator Net sent
> Operator Paged
> Retries Attempted 0
> Message
> The job failed. The Job was invoked by User BIZCHAIR\DarleneMurphy. The
> last step to run was step 1 (CombinationTable1).
> I have also tried to run this in the following way:
> USE Combination_Table
> CREATE TABLE GG Products
> AS
> SELECT *
> FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'W:\MASTER DOCUMENTS\Databases\Channel_Advisor_Feed.mdb';'adm in';'', GG
> Products)
> go
>
> Here I get errors regarding nulls and if I pointed to the correct
> database,
> which I have:
> OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
> returned message "'w:\master documents\bizchair-2.mdb' is not a valid
> path.
> Make sure that the path name is spelled correctly and that you are
> connected
> to the server on which the file resides.".
> Msg 7303, Level 16, State 1, Line 2
> Cannot initialize the data source object of OLE DB provider
> "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
>
> Any help with this problem and I hope I have given good information in
> order
> to get help.
>
> Thank you
>
|||I know you already created the DTS package, but have a look at this
link:
http://www.microsoft.com/sql/solutions/migration/access/default.mspx
I use it to convert all of our old Access items with a very good
success rate (after cleaning up the data of course).
On Feb 27, 2:12Xpm, Dee <D...@.discussions.microsoft.com> wrote:
> I hope I am giving the right information here to get help with this.
> I am trying to get information from two tables that are in an access
> database over to sql 2005. XI did this via DTS and saved the import options
> as a file, but when I run this as a job I get the following error: Date X
> 2/27/2008 11:14:08 AM
> Log XJob History (CombinationTable1)
> Step ID X0
> Server XD86J0PD1
> Job Name XCombinationTable1
> Step Name X(Job outcome)
> Duration X00:00:00
> Sql Severity X0
> Sql Message ID X0
> Operator Emailed X
> Operator Net sent X
> Operator Paged X
> Retries Attempted X0
> Message
> The job failed. XThe Job was invoked by User BIZCHAIR\DarleneMurphy. XThe
> last step to run was step 1 (CombinationTable1).
> I have also tried to run this in the following way:
> USE Combination_Table
> CREATE TABLE GG Products
> AS
> SELECT *
> FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'W:\MASTER DOCUMENTS\Databases\Channel_Advisor_Feed.mdb';'adm in';'', GG
> Products)
> go
> Here I get errors regarding nulls and if I pointed to the correct database,
> which I have:
> OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
> returned message "'w:\master documents\bizchair-2.mdb' is not a valid path.. X
> Make sure that the path name is spelled correctly and that you are connected
> to the server on which the file resides.".
> Msg 7303, Level 16, State 1, Line 2
> Cannot initialize the data source object of OLE DB provider
> "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
> Any help with this problem and I hope I have given good information in order
> to get help.
> Thank you