asked on

Issues upgrading VBA app to SQL Server

Hi,
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
docmd.runsql(sql)
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
me.txt_cert_value
or
forms!boq_main!txt_value

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

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

Jim Horn

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

Jim Horn

Here's a previous question that has some links to DAO vs. ADO conversions, and some good advice.
https://www.experts-exchange.com/questions/20178009/adodb-and-dao.html

cstephen100

ASKER

Hi

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,
Thanks
Stephen

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pootle_flump

>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?

HTH

cstephen100

ASKER

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?

Thanks
Stephen

SOLUTION

pootle_flump

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial