x

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 '10 at 11:24 AM in Default

gjl15 gravatar image

gjl15
49 3 4 5

(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 '10 at 01:30 PM

Tom Staab gravatar image

Tom Staab
5.8k 6 8 10

Thanks Tom! You example will be very helpful. One of the modules is evaluating 13 different pieces of data. A couple of them are pretty complex. For example, one of them is a string containing all the members of the local admins group. My code reads a table of 'expected accounts' and returns missing and unexpected account names. Another is a string of Applications with version numbers. I need to report if an App type exists, if the versions match, etc. So, lots of For loops and Arrays, taking strings and breaking them up, etc.. So my challenge is trying to translate those processes into SQL.
Mar 18 '10 at 01:55 PM gjl15
(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 '10 at 12:33 PM

Blackhawk-17 gravatar image

Blackhawk-17
11.8k 28 30 35

Thanks, I'm checking them out.
Mar 18 '10 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1933
x402
x48

asked: Mar 18 '10 at 11:24 AM

Seen: 2345 times

Last Updated: Aug 17 '10 at 01:22 AM