Understanding Modules and SQL

[tfhwargt3]

I have just migrated my data from a local MS access database to an intranet MS SQL server on my LAN. All of my tables and querys are fine. The situation is, I am not really sure what exactly Access Modules translate to in the SQL Server Management Studio. Are they basically stored procedures? If they are, I am assuming I have to re-write all of the code so it is in the stored procedure SQL format.

thanks.

 

[boblarson]

The answer is a little more complicated than that. Modules are not necessarily going to be able to be modified into SQL Server. It all depends on what your modules are doing.

For example, if you had a module that FTP'd a file up to a website, you will find that it is much more difficult to implement that kind of functionality within SQL Server (requiring ActiveX scripting). But, other things can be taken care of with Stored Procedures, or even DTS packages. The cool thing that SQL Server has that Access doesn't have is Jobs. You can schedule things to happen when you want without having to open a program because it is running as a service at all times anyway.

So, hopefully that helps. But, I've been trying to implement some stuff that a bunch of modules are doing within Access and it has been a bit of a bear at times. So, don't expect it to be a quick and easy process.

 

[JoeAtWork]

I am not really sure what exactly Access Modules translate to in the SQL Server Management Studio.

They really have nothing to do with each other.

If you think SQL Server Management Studio is your user interface to your data - it isn't. It is just a graphical interface to your database.

You need to create an inteface for your data. Some possible choices are a web or windows interface created with Visual Sutdio, or you could keep Access as your interface (by linking the SQL Server tables).

 

[tfhwargt3]

Well, I already have an interface for my database. The module I am transferring parses a text file downloaded from a website, uses the data it finds to query access tables, uses that data to insert records into a finalized table of data.

What would be great is that I could download this text file at a certain time every day and then do the module quering process behind the scenes so that my users will never have to deal with the downloading and uploading. It sounds like "Jobs" could accomplish this. Thank you for your help.

Please let me know if anyone thinks "jobs" would be a good thing to look into.

 

[tfhwargt3]

Actually, I do need a little help with the interface deal. I have my server on a LAN at my office. My users use a program that connects to the database remotely from each of their computers. This side of the process works just fine, and they can change individual data in records and such pretty much seamlessly.

However, now, as you know I am migrating this Module over. I used VBA within access using the built in visual studio in access, and it was very easy to use. I would actually just like to know what software to purchase that will get me as close to that style of interaction with the database as possible for general programming needs. As another poster mentioned, the management studio is not the way to go.

I would like to just program either C# or VB code to rewrite this module. I am looking for ease of use but reasonable on functionality. So what's the closes to the Editor in Access, or the best interface to use in everyone's opinion?

 

[JoeAtWork]

The easiest would be just to keep the module you already have. If you create a new Access database, set up links to the SQL Server tables, then import the module, I would think you could do everything you currently can do (although it might run slower).

Is your current VBA code very Access-specific? What I mean is whether it is using built-in Access features (lots of DoCmd stuff), or is it more generic VBA (i.e. you could run it in Word or Excel without modifications)?

 

[tfhwargt3]

What do you mean it might run slower? I am not really concerned with speed, but how would using a VB Module run slower using SQL Express if I take out all of the DoCmd's.

There are only two DoCmd's and they are both used to import the text file.

 

[JoeAtWork]

If you use linked tables in Access it is sometimes slower.

If you are using VB and ADO, and using stored procedures and other such good practices, then you should get pretty good performance.