Generic BE coding in VBA. (1 Viewer)

GregoryWest

Registered User.
Local time
Today, 00:24
Joined
Apr 13, 2014
Messages
161
What I would like to do is code my VBA so that it is as installation independent as possible. Right now the FE is Access 2013, and I would like the BE to be any one of Access 2013, MySQL, Postgresql, SQLServer, Oracle, DB2. Is there a way I can keep 99% of the installation specific code in the ODBC creation?


The end goal, is to take my code, put it in its directory set up the ODBC link to what ever BE database server and DB. Run the program, have it create all the required tabled, indexes and views automatically. Also have the updates to the program automatically update the DB schema as required. I do not need help with the actual SQL to update the schema, but more how do I write code so that the program does not need to know things like the URL/IP of the DB server etc.
 

isladogs

MVP / VIP
Local time
Today, 05:24
Joined
Jan 14, 2017
Messages
18,186
The BE should be tables only.
All code should always be in the FE based on the linked tables which means that in almost all circumstances it won't matter whether those tables are in Excel, another Access dB, SQL Server, MySQL etc.

You can use code in the FE to create or modify the design of BE tables in e.g. Access or SQL Server.
It may be easier to do what you want using DSN-less connection strings rather than setting up ODBC on each workstation.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:24
Joined
Oct 29, 2018
Messages
21,358
I suppose one way is to write up an initialization routine where you would loop through all possible choices and once a match is found, create all the necessary objects for the FE to function properly. Just a thought...
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 05:24
Joined
Sep 12, 2006
Messages
15,614
One difference is that If you use SQL Server backend tables then you need to open a recordset in a different manner, if the table has the identity bit set (autonumber). I am not sure if the required declaration would work with Access tables or not, offhand

Code:
        If backend <> "Access" Then
            Set rst = MyDB.OpenRecordset(strTableName, dbOpenDynaset, dbSeeChanges)
        Else
            Set rst = MyDB.OpenRecordset(strTableName)
        End If

I imagine there may be a few other issues.


[Edit] - obviously the table linking method is different in each case. eg, An access tabledef.connect property is different to the SQL Server one.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:24
Joined
Feb 28, 2001
Messages
27,001
While your idea is nice, the problem is that every one of those possible choices has slight differences in the way they respond to SQL. I am referring to implementation dependencies that affect how you work. For instance, I believe that in SQL server, you MUST have a PK on a table to be able to update it whereas for Access native BE files that is not always true. For Excel, a linked table cannot be updated because Excel linked tables are Read-Only. And other differences like that.

I can see having a lot of things predefined and written generically, but here is the biggest objection I have from a purely theoretical viewpoint... PURPOSE.

A database is normally written with a specific problem / application / goal in mind. You develop the BE tables based on requirements, but you also develop the FE infrastructure with those same requirements. They are hand-in-glove customized for a given purpose. Not just raw data management in the abstract, but data applied with a purpose.

The BE files hold the data but it is the FE files that embody the business rules if this DB is any kind of business model. You are talking about probing the targeted BE database to see its structure - but knowing its purpose from the BE tables alone is nearly impossible. Certainly at least dauntingly difficult. It seems that your idea of walking in without knowing the BE structure would only really happen if you didn't know the FE purpose, and without purpose your FE will do nothing of importance for the situation to which "real" databases are usually applied.

Stated another way... that BE that you wanted to probe for structure didn't grow in a vacuum. Why would your FE have grown up in one?
 

GregoryWest

Registered User.
Local time
Today, 00:24
Joined
Apr 13, 2014
Messages
161
Yea was thinking might not be the easiest thing in the world. The reason for the different BE is just to make things a little easier for the end user. Mount the DB on the DB server they already have running.


As for the DB just containing tables, not it will contain tables, and Views for sure. Maybe even triggers (although I know those are NOT DB independent) From what I am seeing doing this is a pipe dream, which is where I was sort of leaning towards. Thought I would ask just in case someone else figured out away to actually do it.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:24
Joined
Feb 28, 2001
Messages
27,001
Oh, don't give up on the idea. You can still do generic stuff pretty well. I did some generic routines myself on a big project. Try THIS idea on for size.

I had a bunch of tables. They were going to need maintenance. So I build a form for single-table maintenance. It had built-in command buttons and event routines for functions such as CREATE (a new entry), REMOVE (an extant entry), COMMIT (changes on the current record), CANCEL (pending changes without update), CLOSE (the form), and a couple of other functions. EVERY TABLE was going to need a form to do those things. So I build the form (unbound) first. It had code that would allow me to change colors on controls based on form state, for which I had a generic form-state subroutine that would visit all the controls on a form to do state updates. The state code also would make inappropriate command buttons vanish or appear as needed. For instance, if the from was being used to edit a record, the CREATE, REMOVE, and CLOSE buttons would vanish. All you could do was COMMIT or CANCEL. But if the form was NOT "dirty" then you could CLOSE, CREATE, or REMOVE.

I populated the event routines with error trap code. I had security code that would check for the user's role. I had things to trap updates from unauthorized methods (like making edits and then trying to navigate without clicking either CANCEL or COMMIT.) All of the safeguards when into this form that I called "SngTblTemplate" (single-table template).

From THAT form, I made copies, one for each table, and finished the process of adding bound countrols and the appropriate recordsource on the copy. By using a template form with a lot of the inftrastructure built-in, I got rid of between 40% and 50% of the repetitive work involved in building complex, functional forms. Not to mention that I assured that the forms would have a unified "look and feel" about them because they all behaved predictably.

Therefore, don't let me stop you from thinking about ways to do generic stuff ahead of time and put those generic tools in your toolkit. Everyone needs a toolkit. Make one! Built it up over time. The idea of a generic handler FE can only go so far - but that should not stop you from making re-usable tools.
 

Users who are viewing this thread

Top Bottom