Convert Access Modules to SQL (store procedures)

Question

Hello, I need to migrate a large Access Module, to perform it's evaluation onto a SQL Server.

Background: I have developed two Access modules (VBA) for a customer. Each day, data come in, in the form of a CSV file. The information is computer system information (free space, sp, etc.) from a number of machines. I import it into a table. There are several other tables which have 'default' information (expected sp level, minimum free space, etc.) The module is executed to identify which properties are outside of the defaults, for each machine. The results are written to an 'exception' table.

Requirement: There is a large amount of code in the module. I have now been asked to get this all onto an SQL 2005 server. I am an SQL novice. As best I can tell, the process would be to write the operations performed by the Access Module, into stored procedures.

I have looked at SSIS, but other than importing the CSV file, it doesn't appear that it will be any more help to me. The database has been upsized to the SQL server, and the module is now executed locally on my machine against that server.

Questions: I'm looking for some basic direction: - Are stored procedures the thing I'm looking to build? - Is there any 'magic' that will suck up the Access Module and make it usable, locally, by the SQL server? (that can then be automatically executed) - Or, do I need to look at the VBA code, and re-write it all as SPs in T-SQL.

Thanks for any input!

Answer 1

You asked "Are stored procedures the thing I'm looking to build?", so I feel compelled to respond with that very popular answer: it depends.

Seriously, though, this is difficult to answer without knowing more about your code. If the purpose of the entire module is to compare incoming data vs. acceptable limits/ranges, it's possible the whole thing could be replaced with a simple stored procedure. With that in mind, I will attempt to write a very basic example using this approach.

DECLARE @min_SP tinyint;
DECLARE @min_free_space int;

-- assuming a basic machine_limits table with 1 row; separate column per setting
SELECT TOP(1)
    @min_SP = min_sp
    , @min_free_space = min_free_space
FROM machine_limits;

-- insert machines that need a service pack update
INSERT INTO dbo.exceptions
    (machine_id, reason)
    SELECT m.machine_id, 'service pack = ' + CAST(m.sp AS varchar(10))
    FROM dbo.machines m
    WHERE m.sp < @min_sp
;

-- insert machines that need more free space
INSERT INTO dbo.exceptions
    (machine_id, reason)
    SELECT m.machine_id, 'not enough free space (' + CAST(m.free_space AS varchar(20)) + ')'
    FROM dbo.machines m
    WHERE m.free_space < @min_free_space
;

Answer 2

0

You can start by looking at ActiveX Scripts in SSIS.

Then take a peek at a peer's similar issue.

The secret is to run your code in subs and add a MAIN() as an entry point.

answered Mar 18 '10 at 12:33 PM

Blackhawk-17
10.5k ● 23 ● 29 ● 34

Thanks, I'm checking them out.

Mar 18 '10 at 01:56 PM gjl15

(comments are locked)

Convert Access Modules to SQL (store procedures)

Follow this question

Related Questions