Changing from DAO to MS ActiveX ADO

[Bruce Le Favre]

After upsizing ACCESS to SQL Server I'm having problem to add a record. From on the manual I read that I need to change DAO object mode to ADO object model. That will require some changes in DAO code in VB. My problem I cannot see that DAO code in editable VB format - only in .dll format.

Thanks for any help

[MMcCarthy]

After upsizing ACCESS to SQL Server I'm having problem to add a record. From on the manual I read that I need to change DAO object mode to ADO object model. That will require some changes in DAO code in VB. My problem I cannot see that DAO code in editable VB format - only in .dll format.

Thanks for any help

Because you are not using Access anymore then I would suggest that this post belongs in the Visual Basic forum as they are more likely to be able to help you.

I can move it for you if you want, just let me know.

[pks00]

After upsizing ACCESS to SQL Server I'm having problem to add a record. From on the manual I read that I need to change DAO object mode to ADO object model. That will require some changes in DAO code in VB. My problem I cannot see that DAO code in editable VB format - only in .dll format.

Thanks for any help

I can move it for you if you want, just let me know.[/quote]

Can I assume u have a ADP now? or are u using linked tables with a MDB
What do u mean by DLL format? Are you saying u access code via api's in a DLL?

DAO is perfect for Access, ADO is better suited for non Jet DB's, so I think anyways.

Some differences between DAO and ADO

if u add a record, u still do AddNew and Update on the recordsets
But with edits, in DAO u had to perform a Edit, with ADO u dont
Also the connection strings will be different

But Im confused by your DLL format

[Bruce Le Favre]

I have no .dll problem more... The .mde file I had was locked that why I could not have see VB code... But I still have a problem to add a record in ACCESS app. with upsized to SQL Server back end. Someone suggested that I need to change from DAO to ADO... It will require a lot of code change, that I cannot do, because of proprietary rights.
So my question now...Is it a necessary to change to ADO in order to have functional application... and if it is not what should I change to add record?

I basically don't know VB, but have to make it happen....
Thanks for any help.

[MMcCarthy]

I have no .dll problem more... The .mde file I had was locked that why I could not have see VB code... But I still have a problem to add a record in ACCESS app. with upsized to SQL Server back end. Someone suggested that I need to change from DAO to ADO... It will require a lot of code change, that I cannot do, because of proprietary rights.
So my question now...Is it a necessary to change to ADO in order to have functional application... and if it is not what should I change to add record?

I basically don't know VB, but have to make it happen....
Thanks for any help.

As long as you're using Access you are using the Jet Engine and DAO is better for this. A lot of people will give different opinions on this and you will see plenty of arguements on the matter even on this site.

However, I can definately state that you don't have to change from DAO.

When upsizing the backend to SQL server the best thing to do is to create an odbc DSN connection between the access frontend and the SQL server then just link the tables from sql to the frontend mdb or mde. You will probably have to do this in an mdb before converting it to an mde.

You can also look at creating pass-thru sql queries which are created in access but run against the sql server and therefore written in sql server syntax. Check out the following tutorial on writing these kind of queries.

http://www.techonthenet.com/access/t...h/basics01.php

[NeoPa]

Be aware, Pass-Thru queries are very like using native Transact-SQL (MSSQL Srvr's version of SQL) itself.
Display and string manipulation are cr*p.
Access has MUCH better facilities for display.
Don't get me wrong - I admin SQL Server too and it's hard to beat on performance but display...
In short Back-end Great - Front-End Rubbish.

[Bruce Le Favre]

Thank you all for reply...
Now back to my original problem...Having ACCESS as front end and SQL Server as a back end I'm able to see all look up tables and update record - so that prove that my connection to SQL Server database is working. Now when I try to add new case (record) I'm getting error:' Error #3027 was generated by DAO.Recordset cannot update. Database or object is read-only'. It looks like that login I'm using (SA) doesn't have permission to update certain table in SQL Sever DB. But all tables have all permission (select, insert, update, delete) for 'public'... What am I missing?

[MMcCarthy]

Thank you all for reply...
Now back to my original problem...Having ACCESS as front end and SQL Server as a back end I'm able to see all look up tables and update record - so that prove that my connection to SQL Server database is working. Now when I try to add new case (record) I'm getting error:' Error #3027 was generated by DAO.Recordset cannot update. Database or object is read-only'. It looks like that login I'm using (SA) doesn't have permission to update certain table in SQL Sever DB. But all tables have all permission (select, insert, update, delete) for 'public'... What am I missing?

You need to look at the permissions in the SQL Server DB. As they are linked the R/W permissions of the SQL Server will apply. I'm not a DBA but I know you need to set the permissions here as well.

[NeoPa]

If you're using (sa) as a login, then it's very unlikely that the permissions are the problem.
It's possible, but if so, then the situation is so complicated as to require on site help.
In this case I very much doubt it - anyone setting up that level of complexity in their permissions is not going to let a developer anywhere near the sa account.
I can't think what else might be the problem unless it is the ODBC connection used. I understand you can have ODBC connections which are specifically R/O. Check for that, but otherwise this isn't a problem likely to be solved in a forum (You may want to try it in the MS SQL Server forum), you will need expert help (on site so they can actually understand the situation in its entirety).