VB to SQL Server

[LonnieJohnson]

I have a lot of databases in MS Access that update information each night in varoius tables. I have moved the tables to SQL Server, but still run the VBA code against those tables with them linked to an Access databse.

I was wondering if it was easy to convert VBA to SQL Server? Is there an environment in SQL Server to write VB or VB Script to process against data?

It's gotta be possible!

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!

 

[camaleonCHILE]

Lonnie, I'm not sure about what you want, anyway in SQL SERVER 2000 exists a tool named 'DTS' where you can write code in vb or vbscript ( DTS was designed for data migration). If you want more info, click this

http://www.sqldts.com

regards
cmn

 

[DotNetGnat]

I was wondering if it was easy to convert VBA to SQL Server?

with that i think you meant that you want to create a front end that would directly talk to SQL Server instead of the intermediary link with the Access database...

since you are comfortable with VB, i would suggest you to look into VB.NET.

-DNG

 

[Catadmin]

You can use SQL Strings in VB, but since I'm not a VB programmer, I don't know the exact syntax. I do know that you have to declare a SQL String, then pass your SQL command to that string encased in double quotes. That's about the limit of my knowledge.

As far as taking your code and putting it into SQL and running it from SQL Server, there is no good way to do that except in the DTS package design feature that cmn mentioned.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[LonnieJohnson]

Thanks everyone. I think the suggestions about the DTS VB Script may be my next road of investigation. I want to actually do away with the access database doing the processing and put as much of it in SQL Server as I can.

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!

 

[mrdenny]

I would recommend converting the VB code to T/SQL.

With the VB Code you are probably processing each row one at a time. By comverting to T/SQL you can process everything in a single batch which is much faster.

T/SQL gives you the ability to process row by row if it's truly needed.

T/SQL is the language of SQL Server. Running VB code in a DTS package will be much slower than using the native T/SQL. There will be a learning curve but it shouldn't be to bad.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[Catadmin]

BTW, I'm curious to know what your VB code does and how it's accessed by the user.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[LonnieJohnson]

The code is actually various functions and procedures that set values and update fields each night. We have a nightly process that calculate a rate, assign a code based on various conditions, it may move a record into another table to create a history.

There are about 30 procedure/functions that get run in VBA by a windows schedular. I was able to move some that were simple. The one I am moving right now calculates the tier of a client based on when they came into service with us. It gets complicated because if they came into service prior to July 2005, then their calculation is different than post July 2005.

I was actually able to convert it to T-SQL.

Half of these nightly jobs are calculated fields that I have been trying to get the management to move into a view. They are a small shop and they are use to flat files and it is hard pulling them away. Just 5 years ago EVERYTHING was dbase. Access, dotNet and SQL Server were totally unkown to them.

I am working on it.

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!