Go Premium for a chance to win a PS4. Enter to Win


Issues upgrading VBA app to  SQL Server

Posted on 2006-07-20
Medium Priority
Last Modified: 2013-12-25

    I have a VBA app, which I am know connecting to a sql server db.  I used to link frontend to access db and now its connect to sql server db.

Certain forms work fine, but I am getting different results and errors from differnent events, I suppose its to be expected, but some i am confused about.

1:  In the VBA app, I connected to databases as follows
dim db as database
dim rst as recordset
set db = currentdb
sql = "select *"
Set rst = db.OpenRecordset(sql)

' This works fine in VBA, but after sql server conversio it works with some forms and not otheres. so I have to use the following

Set rst = CurrentDb.OpenRecordset(sql, dbOpenDynaset, dbSeeChanges)

Very unsure why this works with some and not the others.

Im also getting issues with
for certain sql functions.

2:  The vba app is running slower since conversion, i.e calculations are taking longer, in my app i use a lot of the following code

Can this slow my app down when connected to sql server?

Hope someone can assist in some way,
any advice would be extremly helpfull

Question by:cstephen100
  • 3
  • 2
  • 2
LVL 66

Expert Comment

by:Jim Horn
ID: 17147913
DAO was optimized for Access '97 and the Jet database Engine.
ADO is optimized for SQL Server.  I highly recommend converting your code from DAO to ADO.  (How much time do you have?)

Dim cn as ADODB.Connection
Set cn = CurrentProject.Connection

Dim rs as ADODB.Recordset
Set rs = New ADODB.Recordset

Dim sql as String
sql = "select * FROM TableName"

rs.Open sql, cn
LVL 66

Expert Comment

by:Jim Horn
ID: 17147963
Here's a previous question that has some links to DAO vs. ADO conversions, and some good advice.

Author Comment

ID: 17152114

Thanks for reply jim.

Time is a factor, but we are to eventually upgrade all system to vb.net,
i was just testing of feasable it is to have the VBA application connect to a sql server database.
Would I be correct in thinking ADO is what you would use in vb.net.

I find some of the subforms etc dont work as well under vba linked to sql sevrer.  Would it take much time to convert all to ADo or is it quic k process,
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 66

Accepted Solution

Jim Horn earned 1000 total points
ID: 17153854
>we are to eventually upgrade all system to vb.net,
In that case, you'll be going down the road of ADO anyways...

> feasable it is to have the VBA application connect to a sql server database.
Yep.  Office VBA applications can easily connect to SQL.  I currently do it all the time with Access and Excel.

>Would I be correct in thinking ADO is what you would use in vb.net.
Yep.  To get a better answer, you can ask a new question in the VB.NET TA, as many VB programmers are VB6 without the .NET experience, including myself.

>I find some of the subforms etc dont work as well under vba linked to sql sevrer.
I have not come across this yet.  In fact, my current project does an Access subform whose data source is a pass-through query to SQL SP, edited by users selecting values in combo boxes, which edits the pass-through query call, and the speed is identical to when it was a straight (although highly confusing) Access query of linked SQL tables.

Hope this helps.
LVL 11

Expert Comment

ID: 17158441
>Would I be correct in thinking ADO is what you would use in vb.net.
The name is the same but the objects and principles are rather different. Two biggies is there is no longer a recordset (instead datasets and datareaders) and the whole principal is to work in a disconnected environment.

Porting tables from Access to SQL sever is unlikely to yield much benefit on its own. Really you need to transfer as much of the SQL to SQL Server as possible (to be accessed via SPs and views as much as possible). The currentDb code above suggests you have just linked to SQL Server. If you go to .NET then you will have to move your SQL to the server anyway. If you port it from Access to SQL Server now then you can just point your .NET app at those SPs and views when you come to upgrading your front end.

Gotta say - I can't see how
sql = "select *"
works with anything. Typo?

Not sure what 2 means - are you setting a value of a textbox or refering to the value in a query?


Author Comment

ID: 17159607

Hi guys,
Thanks for replies..

sql = "select *", it is a typo surely, The code in 2: is just examples of how I access variables on forms, not doing anything above, just typos.

Im not sure what you ment by SPs,  and placing sql on sql server, do you mean calling scripts etc..  Or do you mean using xml as interface between front end and sql server?

Is Dao slower than ADO?  and by connectin vba to sql server by ado is it faster?


LVL 11

Assisted Solution

pootle_flump earned 600 total points
ID: 17161467
I personally like DAO when connecting to JET (Access) and ADO when connecting to SQL Server. Many would say you should use ADO for both. Few would advocate DAO when connecting with SQL Server. MS would like to dump DAO altogether - it is no longer being developed.

SPs = stored procedure. Basically kind of like a SQL Server code procedure that is written in T-SQL (Transact SQL - SQL Server's version of SQL and more ANSI compliant than JET SQL) and can perform multiple actions and can return dataset(s). It can also accept parameters. You can use this (for example) to return the results of a query using the parameters sent to the procedure to limit the results.

Views = Very similar to access stored queries but you CAN'T pass parameters to them.



Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question