Create Backend Tables Using DAO (1 Viewer)

mtn

Registered User.
Local time
Today, 15:45
Joined
Jun 8, 2009
Messages
54
Hello Everyone,

The idea of what I want is simple. I want to be able to deploy my front end to a user without the back end. All the user will need to do the first time he needs to use the app is click on a button to create the data file.

A little bit of HOW TO do this brought me this far:


Code:
'Original source: http://www.vbforums.com/showthread.php?t=301219
Function CreateVendorTables()

    Dim oRel As DAO.Relation
    Dim oDB As DAO.Database
    Dim otblVendor As DAO.TableDef
    Dim otblVendorContacts As DAO.TableDef
    Dim oIndex As DAO.Index

    'Create new blank access database
    Set oApp = New Access.Application
    oApp.NewCurrentDatabase CurrentProject.Path & "\MyBackEnd.accdb"
    Set oDB = oApp.CurrentDb
    oApp.Visible = True

    'Create first table (tblVendor)
    Set otblVendor = oDB.CreateTableDef("tblVendor")
    With otblVendor

        .Fields.Append .CreateField("VendorID", dbLong)
        .Fields.Append .CreateField("EmployeeID", dbLong)
        .Fields.Append .CreateField("Department", dbText)
        .Fields.Append .CreateField("VendorName", dbText)
        .Fields.Append .CreateField("VendorDescription", dbText)
        .Fields.Append .CreateField("StockSymbol", dbText)
        .Fields.Append .CreateField("VendorStartDate", dbDate)
        .Fields.Append .CreateField("VendorAddress", dbText)
        .Fields.Append .CreateField("VendorCity", dbText)
        .Fields.Append .CreateField("VendorState", dbText)
        .Fields.Append .CreateField("VendorCountry", dbText)
        .Fields.Append .CreateField("VendorNumber", dbText)
        .Fields.Append .CreateField("PaymentMethod", dbText)
        .Fields.Append .CreateField("ConatctTitle", dbText)
        .Fields.Append .CreateField("ContactFirstName", dbText)
        .Fields.Append .CreateField("ContactLastName", dbText)
        .Fields.Append .CreateField("PhoneNumber", dbText)
        .Fields.Append .CreateField("MobileNumber", dbText)
        .Fields.Append .CreateField("FaxNumber", dbText)
        .Fields.Append .CreateField("EmailAddress", dbText)
        .Fields.Append .CreateField("WebAdress", dbText)
        .Fields.Append .CreateField("WikipediaURL", dbText)
        .Fields.Append .CreateField("Notes", dbText)
        .Fields.Append .CreateField("EnteredBy", dbText)
        .Fields.Append .CreateField("EnteredOn", dbText)
        .Fields.Append .CreateField("Deleted", dbBoolean)

        .Fields.Append .CreateField("Suspended", dbBoolean)
        .Fields.Append .CreateField("Terminated", dbBoolean)
        .Fields.Append .CreateField("OnWatchList", dbBoolean)
        .Fields.Append .CreateField("OnWatchListWhy", dbText)
        .Fields.Append .CreateField("OnWatchListAction", dbText)
        .Fields.Append .CreateField("RiskLevel", dbText)
        .Fields.Append .CreateField("RiskLevelNote", dbMemo)
        .Fields.Append .CreateField("OnPreferdList", dbText)
        .Fields.Append .CreateField("IsACompetitor", dbText)
        .Fields.Append .CreateField("IsAnInsider", dbText)
        .Fields.Append .CreateField("CompetitorName", dbText)
        .Fields.Append .CreateField("CompetitorContact", dbText)
        .Fields.Append .CreateField("CompetitorNotes", dbMemo)
        .Fields.Append .CreateField("ServiceType", dbText)
        .Fields.Append .CreateField("ContractNumber", dbText)
        .Fields.Append .CreateField("ContractName", dbText)
        .Fields.Append .CreateField("ContractStatus", dbText)
        .Fields.Append .CreateField("ContractType", dbText)
        .Fields.Append .CreateField("ContractCost", dbCurrency)
        .Fields.Append .CreateField("ContractPeriod", dbText)
        .Fields.Append .CreateField("ContractPeriodNote", dbText)
        .Fields.Append .CreateField("ContractSignersA", dbText)
        .Fields.Append .CreateField("ContractSignersB", dbText)
        .Fields.Append .CreateField("ContractSignersC", dbText)
        .Fields.Append .CreateField("ContractStartDate", dbDate)
        .Fields.Append .CreateField("ContractDate", dbDate)
        .Fields.Append .CreateField("ContractTerminationDate", dbDate)
        .Fields.Append .CreateField("ContractNoticeRequirement", dbText)
        .Fields.Append .CreateField("ContractAutoReview", dbText)
        .Fields.Append .CreateField("ContractAutoReviewInterval", dbText)
        .Fields.Append .CreateField("ContractInsuranceType", dbText)
        .Fields.Append .CreateField("ContractInsurancePremium", dbCurrency)
        .Fields.Append .CreateField("ContractInsuranceEndDate", dbDate)
        .Fields.Append .CreateField("ContractComments", dbMemo)
        .Fields.Append .CreateField("LegalReview", dbText)
        .Fields.Append .CreateField("LegalReviewBy", dbText)
        .Fields.Append .CreateField("LegalNote", dbMemo)
    End With
    oDB.TableDefs.Append otblVendor
    
    'Create an index on tblVendor
    Set oIndex = otblVendor.CreateIndex
    With oIndex
        .Name = "VendorIDIndex"
        .Fields.Append .CreateField("VendorID")
        .Primary = True
    End With
    otblVendor.Indexes.Append oIndex

    'Create second table (tblVendorContacts)
    Set otblVendorContacts = oDB.CreateTableDef("tblVendorContacts")
    With otblVendorContacts
        .Fields.Append .CreateField("ContactID", dbLong)
        .Fields.Append .CreateField("VendorID", dbLong)
        .Fields.Append .CreateField("FirstName", dbText)
        .Fields.Append .CreateField("LastName", dbText)
        .Fields.Append .CreateField("Title", dbText, 20)
        .Fields.Append .CreateField("WorkPhone", dbText, 15)
        .Fields.Append .CreateField("Email", dbText)
        .Fields.Append .CreateField("Notes", dbMemo)
    End With
    oDB.TableDefs.Append otblVendorContacts


    'Create an index on tblVendorContacts
    Set oIndex = Nothing
    Set oIndex = otblVendorContacts.CreateIndex
    With oIndex
        .Name = "VendorIDIndex"
        .Fields.Append .CreateField("ContactID")
        .Primary = True
    End With
    otblVendorContacts.Indexes.Append oIndex


    'Create relationship between tblVendor and tblVendorContacts
    Set oRel = oApp.CurrentDb.CreateRelation("MyRelationship", "tblVendor", "tblVendorContacts", _
                                             dbRelationLeft Or dbRelationUpdateCascade Or dbRelationDeleteCascade)
    oRel.Fields.Append oRel.CreateField("VendorID")
    oRel.Fields("VendorID").ForeignName = "VendorID"
    oApp.CurrentDb.Relations.Append oRel


End Function

While the above works fine and answers 90% of my question, I am however trying to figure out how to automate modifying some of the fields as well.

For example I want the field "Suspended" to have the following properties:

"DisplayControl" to be a check box rather than the default text box that the above function will create. the same goes for DefaultValue, and Format for some of the other fields.

Any help or direction on how to go about this would be very much appreciated.

Thanks.
 

KenHigg

Registered User
Local time
Today, 11:45
Joined
Jun 9, 2004
Messages
13,327
I think you just do something like field(x).property = "abc"

Edit - Or, per Trevors ref to the web site, if you're looping through the fields

.ValidationText = "Birth date cannot be future."
 

mtn

Registered User.
Local time
Today, 15:45
Joined
Jun 8, 2009
Messages
54
Thank you both for your quick response. I have actually visited the link above and while I was able to us this:

Code:
        Set fld = .CreateField("VendorStartDate", dbDate)
        fld.ValidationRule = "Is Null Or <=Date()"
        fld.ValidationText = "Start date cannot be a future date."
        .Fields.Append fld

to set the validation rule and text in place of just using only this:

Code:
.Fields.Append .CreateField("VendorStartDate", dbDate)

I wasn't so lucky using the same approach to set the DisplayControl and or Format of a Boolean field.

I am trying to avoid looping through the tables from the back end after it has been created but to set these "properties" when the tables are been created.

Please let me also ask if my idea about deploying a backend this way is a good idea or not.

Thanks again.
 

KenHigg

Registered User
Local time
Today, 11:45
Joined
Jun 9, 2004
Messages
13,327
One issue at a time :)

In a general sense, one does not 'deploy' a back end, you delpoy the front end.

And... I don't follow your strategy all together but it looks like you may be wanting to set properties that are associated with the input form/control, not the table.
 

mtn

Registered User.
Local time
Today, 15:45
Joined
Jun 8, 2009
Messages
54
That's "true" and while it's very easy for me to deploy my frontend (accdr) which I have been doing along side with the backend, I am only trying to automate creating the back end.
 

KenHigg

Registered User
Local time
Today, 11:45
Joined
Jun 9, 2004
Messages
13,327
So the FE and BE are on the same computer?
 

mtn

Registered User.
Local time
Today, 15:45
Joined
Jun 8, 2009
Messages
54
They are both on the same computer. My app is an invoicing system where the user is able to switch between different company data from the login page. The user can actually create a new company data anytime without any help from me. What I presently do/implement is for the user to click on a button after entering the new company details, a copy of the current backend is created with a different name, previous data are removed from it and the user can connect to it as a new backend file. Automating this with the idea above will be best suited for me if it works well.

I'm only trying to improve on what I already have.
 

KenHigg

Registered User
Local time
Today, 11:45
Joined
Jun 9, 2004
Messages
13,327
Keep in mind this is only my 2 cents but...

Seems creating a whole new table (database?) everytime a new customer is created is going to blow up somewhere down the line no matter how you set it up. It seems a better strategy to add a new customer in the single, existing FE/BE.

edit - If you still insist, why not just make a copy of the existing table?
 
Last edited:

mtn

Registered User.
Local time
Today, 15:45
Joined
Jun 8, 2009
Messages
54
May be I didn't explain myself very well. I am not trying to create a single table but a separate backend data files for say ABC Corp, FGH Logistics Inc., XYZ Limited etc depending on how many company. So a user can switch the backends to create an invoice depending on which company that's going to be from. I appreciate your comments but this is an option I want to try my hand on.
 

KenHigg

Registered User
Local time
Today, 11:45
Joined
Jun 9, 2004
Messages
13,327
So you'll have a one .mdb backend for customer A and another .mdb backend for customer B?
 

spikepl

Eledittingent Beliped
Local time
Today, 16:45
Joined
Nov 3, 2010
Messages
6,144
I am curious: what is the purpose or reason for this?

Aside from that, why all this code when you could provide a virgin backend with your installation, and just copy it to a production version or versions?
 

KenHigg

Registered User
Local time
Today, 11:45
Joined
Jun 9, 2004
Messages
13,327
I am curious: what is the purpose or reason for this?

Aside from that, why all this code when you could provide a virgin backend with your installation, and just copy it to a production version or versions?

That's what I've been try to say but it seems he wants to know why his code does not work so I guess it's futile to suggest another direction - :)
 

mtn

Registered User.
Local time
Today, 15:45
Joined
Jun 8, 2009
Messages
54
I have tried to explain why I am doing this. I have a system that works and very well. Besides all the reasons I listed in my previous post, I am learning how stuff works everyday and I am glad I have found this.

This is what solved my situation for me:

Code:
  'Create the boolean properties...
    With oDB.TableDefs("tblVendor").Fields("Suspended")
    
        .Properties.Append .CreateProperty("DisplayControl", dbInteger, AcControlType.acCheckBox)
        .Properties.Refresh
    
    End With
 

mtn

Registered User.
Local time
Today, 15:45
Joined
Jun 8, 2009
Messages
54
Just a feedback on this again:

I have been able to accomplish all that I wanted to do when I started this thread. To write a code that would create about 70 individual tables into a new back-end will be crazy when you can simply deploy your back-end data file along side with your installation script.

To resolve writing these codes individually, I decided to loop through all my table collections and their fields, get their field types and got another sub function to actually write each separate function that will create each table to my immediate window. All I did was copied all these into a standard module, compiled them and was good to go without any error.

The next step was to get a query that will display all my table relationships showing the foreign keys, foreign name, etc. I also use the same principle above to use another sub function to loop through this Query and write separate functions to my immediate window. I copied all these to another standard module and it compiled without any error.

The next step was to run my subs to first create a new database, create all the tables that I need and then their relationships. I have tried it over and over and on separate machines and it is simply far better for me than all the other approaches I have used in the past to deploy a new back-end.

I know a number of people might not agree with me on why I have to go through the process of writing so much code for this but like I said, I automated the writing of the code and in 1 seconds I have all of them printed to my immediate window.

Also, my download or installation file will be reduced in size as I only need to deploy the front-end and give the users the opportunity to also recreate this with ease.

Thanks.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 15:45
Joined
Sep 12, 2006
Messages
15,614
another approach is to pre-design a blank set of data tables, and just copy and rename that everytime you need a new back end.

i am sure this is far easier than trying to create a back end in code

i have designed a product to do a somewhat similar thing, for updating backends. you "snapshot" the design of the backend on a development machine, and then use that to add any extra tables fields indexes etc to an already deployed backend - so that existing data is not compromised.
 

Users who are viewing this thread

Top Bottom