Usin VB in Sql Server

  • Thread starter Thread starter jschping
  • Start date Start date
J

jschping

Hi,

I Upsized an Access database to SQL Server 2008 Express, so I'll be able to
create a .NET application so the public will be able to view the data.

However, I used Access's wizard because I wanted to keep the reports that I
worked on for days. I thought that the Access front end would still allow
that.

However, I had used some VB functions in the queries that the reports were
built on. Now I get error messages that those functions aren't defined in SQL
Server.

What can I do? Can I port the VB code over to SQL Server so it will
recognize those functions?

Thanks!!
 
SQL Server does not support VB. It only supports SQL and T-SQL
(Transaction-SQL) which effective allows you to develop custom functions
*BUT* they are do not function in the same manner as functions in VBA. They
can be used within VIEWS and Queries executed in SQL Server, however there
will be a learning curve in adapting them.

Your existing queries will work, however you have to run them as
Access-native queries. You will also have to link the Front-End to the SQL
Server backend. Once you relink them you'll then need to rename the links to
the names of the old tables. The Linked Table Manager will most likely
import the links as dbo.tblName. Doing the renaming should be sufficient to
reenable the queries.

Also on a side note, C# is the generally preferred language for the code
behind in a page. Personally, my first ASP.NET site used VB.NET since it
eliminated the learning curve with C#. VB.NET is very similar in context to
VBA and should be easier to transition to. Once you start, you'll want to
visit WWW.ASP.NET very frequently.
 
Recent versions (MS SQL Server 2005 + ) also accept C#/VbNet, but that
requires some extra steps, in comparison with what we have with
Jet+Access+VBA. That is not something use quite often, though, probably for
security reasons, although it may be nice to interact with SQL itself, such
as adding a LIST aggregate for total queries.



Vanderghast, Access MVP
 
Thanks!

David C. Holley said:
SQL Server does not support VB. It only supports SQL and T-SQL
(Transaction-SQL) which effective allows you to develop custom functions
*BUT* they are do not function in the same manner as functions in VBA. They
can be used within VIEWS and Queries executed in SQL Server, however there
will be a learning curve in adapting them.

Your existing queries will work, however you have to run them as
Access-native queries. You will also have to link the Front-End to the SQL
Server backend. Once you relink them you'll then need to rename the links to
the names of the old tables. The Linked Table Manager will most likely
import the links as dbo.tblName. Doing the renaming should be sufficient to
reenable the queries.

Also on a side note, C# is the generally preferred language for the code
behind in a page. Personally, my first ASP.NET site used VB.NET since it
eliminated the learning curve with C#. VB.NET is very similar in context to
VBA and should be easier to transition to. Once you start, you'll want to
visit WWW.ASP.NET very frequently.




.
Click to expand...
 
Older versions (all current versions) also support Extended
Stored Procedures, ie User Defined Functions.

User Defined Functions are defined in Windows DLL's
or ActiveX DLL's.

VB6 can create Windows DLL's (in spite of MS) or
ActiveX DLL's.

What you don't get is the default Access.Application objects
and methods: Codedb, CurrentDB, OpenRecordset etc.

So it's good for calculations and string manipulations, but
not much else.

http://www.devx.com/getHelpOn/10MinuteSolution/20562
http://msdn.microsoft.com/en-us/library/ms164653.aspx

(david)
 
david said:
Older versions (all current versions) also support Extended
Stored Procedures, ie User Defined Functions.

User Defined Functions are defined in Windows DLL's
or ActiveX DLL's.

VB6 can create Windows DLL's (in spite of MS) or
ActiveX DLL's.
Click to expand...

Could you point me to some info re creating windows dll's with vb6?
What you don't get is the default Access.Application objects
and methods: Codedb, CurrentDB, OpenRecordset etc.
Click to expand...

Those are all part of the DAO library, not Access. The DAO lib is usable
from vb6 once you set a reference (or it can be used late-bound as
"DAO.DBEngine.36").
 
Back
Top