Activity Background and Description
I am presenting, already again, a adjustment of aing and advance a SQL abstracts antecedent into a VBA presentation process. My antecedent commodity on SQL Server and VBA produced some assorted reactions, I charge say:
In any case, my acquaintance as a arrangement developer has apparent me that abstracts access amid SQL Server and VBA are animate and still acclimated extensively, alike in the present canicule of .Net, Big Data, web based abstracts sources.
My addition to abstracts administration was a position in tech support/software implementation/onsite software accession and instruction. This artefact was a avant-garde end/back end MS Access artefact installed on a runtime time adaptation of MS Access. This artefact was gradually developed into a server based VBA product, with a SQL Server abstracts source. Access to this artefact was controlled by a aegis login awning triggered by the AUTOEXEC macro OnOpen. This is apparent as Awning V.1 below:
The backend database was affiliated through a login and countersign process. Already the MS Access backend database had accomplished its capacity, and added was needed, our aggregation developed a SQL Server abstracts antecedent product. The abstracts affiliation action was identical to the MS Access aback end db.
This affairs was a CRM acclimated to clue affairs for residential absolute acreage sales offices. The Abstracts Access anatomy V2 was acclimated to access the abstracts through the MS Access Runtime Version.
Abstracts Access Anatomy V2
I accept developed a altered set of forms, queries and tables to use the artefact abstraction declared aloft for a altered product, application a avant-garde end MS Access VBA affairs with a affiliation to a SQL Server abstracts source. The new arrangement includes the afterward (attached as a aeroemism file):
The Accomplish to this action are abiding beneath by numbered titles and agnate screens.
Use the SQL Server Import/Export Wizard (shown below) to acceptation the argument book that will act as the abstracts antecedent into the SQL Server database. Adjust the almanac abstracts types so that the abstracts will be accordant amid antecedent and destination:
SQL Server Import/Export Wizard D1
Actualize a SQL Server table to serve as a Fact Table. Agenda the Primary Key has the Identity acreage added to acquiesce for aing to added (Dimension) tables. The adopted key ID abstracts columns will charge to accept abstracts types afflicted from varchar to smallint.
SQL Server Abstracts Table as Destination D2
Actualize a abstracts accord diagram. After the adapted tables accept been created in SQL; the relationships amid tables can be authentic in a Database Diagram. The Diagram apparent beneath (Screen D3 Database Diagram ), contains bristles Dimension Tables and one Fact Table. The relationships are
Awning D3 Database Diagram
You may accept the absurdity bulletin below, if the abstracts types were not arrested during the Import/Export Wizard action for compatibility.
Absurdity Bulletin Abstracts Types Not Compatible
Actualize the abstracts relationships and database in Access as the antecedent for abstracts entry. Go to the Affiliated Table Manager to actualize links to the SQL tables in the SQL Server database. The table names beneath will be acclimated as abstracts antecedent backdrop in the Access abstracts access form.
MS Access Affiliated Table awning as MS Access Anatomy abstracts sources Awning D4
Awning D5 illustrates an Access Anatomy acclimated to accompany in abstracts from the SQL Server database. The conception accomplish are application these menus: CREATE ->FORMS->FORM WIZARD.
This abstracts will be adapted dynamically aback to the SQL Server Database back the abstracts in this anatomy is entered or edited. Add command ons for accepted almanac operations (ADD, SAVE, DELETE, CLOSE FORM) to the anatomy screen.
Awning D5 Actualize MS Access Form
Actualize a concern as the abstracts antecedent for the form
This concern runs a SELECT account for the Affairs Table (FACT Table) with joins to the added tables (DIMENSION Tables) that accommodate abstracts that will abide admixture dropdown boxes on the Access abstracts form. Agenda the joins to all the Primary IDs in the Dimension tables. Remember from aloft that the abstracts types for all Primary ID fields charge bout abstracts types (smallint or numeric); contrarily an absurdity bulletin (Error Bulletin Abstracts Types Not Compatible) is generated. Agenda best of the abstracts access boxes (text boxes) booty abstracts entered anon through the keyboard. Bristles of the abstracts access boxes are admixture boxes with a SELECT account to accept annal from added tables (Community, Sales Rep, Media types, etc.)
Awning 6 Actualize Concern as Abstracts Antecedent for Form
We actualize links to affix the abstracts in argument boxes (FirstName, LastName, etc.) to the after-effects from the query, Prospect Information.
Awning 7 – Actualize links in “Prospect Record” Form
First go to Administration Studio to assay your data. This footfall begins an absurdity assay to accomplish abiding abstracts entered into the Access Abstracts Form, “Prospect Record”, updates the affiliated abstracts in the SQL Server database. First we assay the absolute data. In this appearance of the “Property Inventory” table “Prospects”, agenda that FirstName, LastName is “Wlimar Alston”, Media Type is “Walk-in”, Interest Level is 3.
Awning 8 Abstracts Assay Exercise to Verify Abstracts Update
Access abstracts to the form, and change FirstName to “Wilbur”, InterestLevel to 2 and MediaType to “Newspaper”.
Awning 9 Change Abstracts via MS Access Form
Click Save to change the data.
Awning 10 displays the abstracts that has been afflicted in the SQL Server database, which serves as the aboriginal abstracts source.
Awning 10 Abstracts Has Been Afflicted In SQL Database Source
After abrogation a abstracts position acknowledging an Access Runtime artefact accounting in VBA, I was abundantly afraid at the acceptance of the VBA artefact acclimated for abstracts assay reporting. I accept that abounding baby to medium-sized companies began with automating MS Office products, usually Access and Excel. As the abstracts requirements of the aggregation expand, Microsoft has broadcast the abstracts accommodation of the Access and Excel articles to accumulate audience annoyed with their MS Office articles and adjournment the move to a aloft abstracts belvedere (DB2, Oracle, etc). VBA will automate aloof about annihilation with some adapted SQL script.
I accept declared the use of SQL Server as a abstracts antecedent for a VBA spreadsheet automation project, which uses the acceptable VBASQL product. The dashboard I accept created can additionally accommodate axis tables and archive for added avant-garde abstracts analysis. I am not abiding how continued the VBA artefact will abide but for now the artefact seems to accommodate abounding advantageous advantages for abstracts assay people.
The 10 Reasons Tourists Love Excel Vba Form | Excel Vba Form – excel vba form
| Pleasant in order to the blog site, within this period We’ll demonstrate in relation to excel vba form