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';'admin';'', 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 youDee
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';'admin';'', 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:12=A0pm, 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. =A0I did this via DTS and saved the import opti=ons
> as a file, but when I run this as a job I get the following error: Date ==A0
> 2/27/2008 11:14:08 AM
> Log =A0Job History (CombinationTable1)
> Step ID =A00
> Server =A0D86J0PD1
> Job Name =A0CombinationTable1
> Step Name =A0(Job outcome)
> Duration =A000:00:00
> Sql Severity =A00
> Sql Message ID =A00
> Operator Emailed =A0
> Operator Net sent =A0
> Operator Paged =A0
> Retries Attempted =A00
> Message
> The job failed. =A0The Job was invoked by User BIZCHAIR\DarleneMurphy. =A0=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';'admin';'', 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=. =A0
> Make sure that the path name is spelled correctly and that you are connect=ed
> 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 ord=er
> to get help.
> Thank you