Geek stuff
So, I'm slowly getting the hang of this Access 2003 crap, but there are a few conceptual issues that just aren't sitting well with me.
The application uses MS-SQL 2000 as a back-end and Access 2003 as a client. The Access client has link tables, and the VBA uses ADO to connect to those link tables. It looks like ADO could just be used to connect to the MS-SQL back-end, and thus be able to use stored procedures and other goodness. Is there a reason to use link tables instead? Keep in mind the following question as well.
The client has many, many forms, all with unbound fields for data entry and display. Instead, they use VBA scripts to both populate the initial data and to send any adjustments or new records to the SQL back-end (via the link tables). Why not use bound controls? Are there reliability, performance, or other issues?
This one requires an example. I have a dbo.person table that stores information about, well, people. I have a dbo.org table that stores information about organizations. One of the fields in the dbo.org table refers to a person record (CEO, maybe, or primary contact). I have an Access form that creates a new dbo.org record. Easy. But when searching for which person to make the primary contact, there's a 'new' button in case the person isn't already in the database, which then opens a sub-form to add a dbo.person record. When the subform is done processing, what's the most elegant way to pass the index for the newly created dbo.person record back to the original dbo.org-record-creating form so that the primary contact field can be populated correctly?
Of course my first real modification project has to cut right to the heart of the entire application, touching on all the core tables and needing to find and/or create records from a zillion different places. Gah! Brain pain!
The application uses MS-SQL 2000 as a back-end and Access 2003 as a client. The Access client has link tables, and the VBA uses ADO to connect to those link tables. It looks like ADO could just be used to connect to the MS-SQL back-end, and thus be able to use stored procedures and other goodness. Is there a reason to use link tables instead? Keep in mind the following question as well.
The client has many, many forms, all with unbound fields for data entry and display. Instead, they use VBA scripts to both populate the initial data and to send any adjustments or new records to the SQL back-end (via the link tables). Why not use bound controls? Are there reliability, performance, or other issues?
This one requires an example. I have a dbo.person table that stores information about, well, people. I have a dbo.org table that stores information about organizations. One of the fields in the dbo.org table refers to a person record (CEO, maybe, or primary contact). I have an Access form that creates a new dbo.org record. Easy. But when searching for which person to make the primary contact, there's a 'new' button in case the person isn't already in the database, which then opens a sub-form to add a dbo.person record. When the subform is done processing, what's the most elegant way to pass the index for the newly created dbo.person record back to the original dbo.org-record-creating form so that the primary contact field can be populated correctly?
Of course my first real modification project has to cut right to the heart of the entire application, touching on all the core tables and needing to find and/or create records from a zillion different places. Gah! Brain pain!