Executing Access VBA in a DTS package

#1

hi,

i have an access project with a rather complex vba code. This code was used to load input files into the access database. The problem with these files is that they do not have a cr/lf specified, so I had to write code to parse the entire file and finding specific keywords within the file so I could load them in a table.

Now we need to transfer this access db into SQLServer. I would like to execute the access module within a DTS package so I do not have to re-write it again.

I tried to create a job within SQLserver (2000) with a step executing access from the comand line. It did not work.

Can I call access from a VBScript

I would appreciate your responses

Luis

#2

You can yes.

I would put your code into a macro and call the macro. Note though that if
you schedule this

A. You will need Access on the Server
B. MS does not support calling Access unattended Server side.

I would obviously prefer to redo this in VBScript but if this is a major
undertaking then this is a solution

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"LAB" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Quote:

hi,

i have an access project with a rather complex vba code. This code was
used to load input files into the access database. The problem with these

files is that they do not have a cr/lf specified, so I had to write code to
parse the entire file and finding specific keywords within the file so I
could load them in a table.

Quote:

Now we need to transfer this access db into SQLServer. I would like to
execute the access module within a DTS package so I do not have to re-write

it again.

Quote:

I tried to create a job within SQLserver (2000) with a step executing
access from the comand line. It did not work.

Can I call access from a VBScript?

I would appreciate your responses,

Luisa

#3

Allan

Thank you for your reply. Examining the files I discovered that each line is ending with a LF instead of a CR. Is there a quick way to mass change the LF character for CR (chr(13)) in VBscript?. in this way, I could load the file in a temp table in SQLserver and write a much easier stored procedure to convert it to the required table

Thank you agai

Luis

#4

To save converting, in DTS you can just select the row delimiter to be CR
only (or pretty much anything you want for that matter.)

--
Darren Green
http://www.sqldts.com

"LAB" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Quote:

Allan,

Thank you for your reply. Examining the files I discovered that each line
is ending with a LF instead of a CR. Is there a quick way to mass change the

LF character for CR (chr(13)) in VBscript?. in this way, I could load the
file in a temp table in SQLserver and write a much easier stored procedure
to convert it to the required table.

Quote:

Thank you again

Luisa

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Executing Access VBA in a DTS package

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Executing Access VBA in a DTS package - 02-09-2004 , 01:21 PM

Re: Executing Access VBA in a DTS package - 02-09-2004 , 01:58 PM

Re: Executing Access VBA in a DTS package - 02-09-2004 , 05:21 PM

Re: Executing Access VBA in a DTS package - 02-10-2004 , 03:19 AM