Hi all.
I have been using access for 20 yrs and skilled in vba and dao. I am moving my backend to sql server from Sharepoint lists - I work for a large corporation and IT hates me. I am an engineer that learned to do what I want without IT. I used sharepoint lists for an app I did because the use is global - that was the only way I new how to do it at the time. The db is all form driven. Finally, I got IT to give me a database on a shared sql server. I have a colleague that has some sql server experience so she will be the DBA for all of my stuff. So, to convert my access db to use sql server tables, I have a couple questions...
From what I read, I need to move from dao to ado. I did a quick test... exported my sharepoint lists (12 tables, approx 2000 lines each with 25 columns) to the server, named the tables the same and linked them. Ran the db. The dao rs.findfirst is very slow - this is why I assume I need to move to ado. My question... My standard designing mode was to create an access query (not vba, not sql) to query the sharepoint list. Then I dao the access query in vb to do my recordset manipulation. When converting to ado for the sql server, do I still do the same thing or do I connect the recordset directly to the sql server table and not go thru the access query? which method is faster? Which method is easier to develop? I assume that I can still have my vb ado recordset point to an access query that uses the sql server table.
Any other advice would be helpful
Thanks.
Steve