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:
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.
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.