Convert Access Modules to SQL (store procedures)

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!

more ▼

asked Mar 18, 2010 at 11:24 AM in Default

avatar image

49 4 4 7

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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 ;

more ▼

answered Mar 18, 2010 at 01:30 PM

avatar image

Tom Staab ♦
14.5k 7 15 21

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Mar 18, 2010 at 12:33 PM

avatar image

12.1k 30 36 42

Thanks, I'm checking them out.

Mar 18, 2010 at 01:56 PM gjl15
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 18, 2010 at 11:24 AM

Seen: 3291 times

Last Updated: Aug 17, 2010 at 01:22 AM

Copyright 2018 Redgate Software. Privacy Policy