Posted - 06/19/2012 : 17:24:41
| Hello Community-
I'm doing a small consulting project for Medical partnership that runs Allscripts patient manager system which is a SQL Server based. The project involves using an excel form to pull data (just a one way data pull), then the surgical coordinator will enter a bunch of other information, submit the form, information from the form will then update an Access database, then the information will flow into the 17 different forms required to authorize, bill, etc.
I've done some SQL server work in the past, but my primary strength connecting Excel via ADO to various systems and building simple dashboards.. So here are my key questions:
1) The data that I will pull into the form pulls from a bunch of views and tables.. Typically, I a data layer behind my form, so that the SQL for form is very simple. Should I do this in SQL Server (not sure how locked down the application is, or if I can build other tables outside of the software) or in Access. I'm thinking it may be easier in Access.
2) I'm doing most of this project offsite. The IT guy provided an IP address/logon etc. for the SQL server box. I'm trying to use Access to connect the tables SQL server via ODBC (or OLE DB if that is easier). The IT guy was not very knowledgeable about firewalls, and the like, and I suspect I may not be able to just connect with ODBC, without some type of additional configuration on the server.
They also set me up with this terminal server application, which is similar to Goto my PC type program, where I'm effectively logged on to their system. Unfortunately I can't use the applications on my system (e.g. MS Access to link to tables)..
My apologies for these simple questions.. from a networking/server perspective, I'm used to having more support.