Hi All!
So, here is what I have. I work for a large company (our IT dept does not like me so much)... I wrote an access db for my business unit to manage data - inputs coming from global project managers. The only way I knew of to do this for global access was by using sharepoint lists. The sharepoint lists are link lists in the database. I use DAO routines to modify / edit / update the sharepoint lists recordsets. The Db works well - not huge... after 2 years, the largest of the 12 tables is 2000 lines long with 30 columns.
Aside from this db, I have server experience... well, I read data from all kinds of sources for reporting in access, excel, power query... I connect to sql server, denodo, oracle... So I have experience reading, but none writing.
I am looking moving my db from sharepoint lists to sql server. I don't know much about this so I have help from a colleague that has sql server experience but no access experience. We now have our own server setup on a shared server. We have a login with read/write access.
My questions is...
1) Can I just move my data from the sharepoint lists to tables on the sql server, re-link the tables and go???
2) I had my colleague create a table on the server, I linked to the table in Access, opened the table in table view mode and I was not able to modify any of the data? I can do this on the sharepoint list.
3) I tried to create an update query (not in sql) and was not able to update the sql server table. Do I need to do passthru queries to modify / append / delete data in table or can I use the standard access design mode?
Thanks for the help.
Steve