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!
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.
answered Mar 18 '10 at 01:30 PM