DataBase - Usin VB in Sql Server

Asked By jschping

23-Dec-09 12:48 PM

Hi,

I Upsized an Access database to SQL Server 2008 Express, so I will 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 are not 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!!

23-Dec-09 11:31 PM

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 will 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 will want to
visit WWW.ASP.NET very frequently.

24-Dec-09 09:05 AM

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

25-Dec-09 10:21 AM

Thanks!

27-Dec-09 08:43 PM

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 do not get is the default Access.Application objects
and methods: Codedb, CurrentDB, OpenRecordset etc.

So it is 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)

27-Dec-09 10:04 PM

Could you point me to some info re creating windows dll's with vb6?


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

30-Dec-09 03:47 AM

http://www.vb-helper.com/howto_make_standard_dll.html

VB6 uses the Visual Studio 6 compiler and linker. You
need to interrupt the process and change the link commands.

(david)

30-Dec-09 05:51 PM

Thanks v much. Studying it now...
Happy new year!