Build a Sample Customer Database Tool

While it would be easy to scrape a database off the internet, the legality of this is questionable.  So what is a VBA developer to do?  The answer is to build one from scratch.  In this Case Study, I use Excel, VBA, and Access to create a customer database using my free Build a Sample Database Tool to create a credit card operations customer Table .l.  The tool will allow you to build any size customer table. An effort was taken to make it as real as possible and therefore useful.  With the code provided, you could easily modify it to add fields like a phone number and a social security number

The complete tool, ready to run is available free for download below.

I do this by using lists of the most popular first, last, and street names in the US.  I then randomly create a Customer Name, Address, and Phone Number. Of course, you could use the code for whatever type of business customer you would like to simulate. 

This Database Build Tool Case Study will provide the apprentice VBA user with exposure to VBA code, VBA forms, some SQL (and more importantly keeps me out of court).  I’ll also add a form to allow the user to decide just how many customers they will want to start with. Now let’s get started.

If you download the workbook, you will see the sheet “Database Building Blocks” pictured below where I have provided all the source data, we will need to create our Database.  Guess what? We will do it all through VBA.

Before we start we are going to need to:
1.  Create a bank Access database (see the schema below).
2.  Look up the connection string if you have an older version of Access  (the version I’m using will work
for 2013/2016). The schema (tables) of the Access database is shown below.  We will build it through VBA code.
3.  So all you will need to do is create a blank Access database Replace the full name and path on cell “E1”.,
build any size sample customer database.
Build any size database with one click 

Sub Build_a_database()

'* **********************************************************************
' ** Date:          12/2/2018
' ** Developer:     Ray Mills
' ** Purpose        Builds a Sample Database tool and demonstrates
' **                VBA, SQL, Connection strings, ADODB and recordsets
' ** Note: add the following references (choose Tools then References
' **       from the VBE menu)
' ** 1)Microsoft Activex Data Ojects 6.1 Library
' ** 2)Microsoft Activex Data Ojects Recordset 6.0 Library
' **********************************************************************

Dim sAccessDB As String
Dim oconn As New ADODB.Connection
Dim sConn As String, sSQL As String

'capture accessdb path and filename ...
sAccessDB = Sheets("Database Building Blocks").Range("E1").Value

' lets create the connection string ...
sConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sAccessDB & _
";Persist Security Info=False;"

' lets create the connection to our access db ...
oconn.Open sConn

 ' lets write the SQL to create the account table ...
sSQL = "CREATE TABLE Account " & _
"(Month_Ending DATE NOT NULL, " & _
"Card_Nb Double NOT NULL Primary Key, " & _
"Acct_NB Double, " & _
"Cust_NB Double, " & _
"Account_Open_Date DATE, " & _
"Account_Close_Date DATE, " & _
"Last_Statement_Acct_Bal MONEY, " & _
"Last_Statement_Min_Pay MONEY, " & _
"Purchase_Interest_Rate FLOAT, " & _
"Statement_Day BYTE);"

oconn.Execute sSQL

' clean up ...
oConn.close 
set oconn= nothing

end sub

'Note: For the sake of brevity I only displayed the code for creation of the account 
'table. Creation of the other tables is very similar. The complete code is available
'for free for download on this page.
We have a list of common first, last, and street names as well as zip codes, towns, and states. The question is how to put them together for input into the Customer table?  The answer lies in the two ‘Randomizer’ VBA Functions below.  So if we knew we wanted 4% of our customers to have (B)usiness accounts and the remainder to be (P)ersonal, we could set the random_num(100) and if it came back of 4 or less we make the acct a “P”.  We can use the random_num_range to randomly create the Card, Account and Customer numbers.
Function random_num(x As Long) As Long
  'Simple random Number generator function …
     Randomize
     random_number = Int(x * Rnd) + 1
     random_num = random_number

End Function


Function random_num_range(upperbound As Double, lowerbound As Double) As Double
   'random number generator function with upper and lower limits …
   Randomize
     random_number = Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
     random_num_range = random_number

End Function
Let’s put those random number functions to work.  Below find the create a customer code.  Here’s what it does:
1. It randomly chooses whether we create a male or female customer.
2. It randomly selects the first name from column A or B.
3. It Randomly selects the last name from column C.
and it does the same for the rest of our fields…
Sub Create_A_Customer(oConn As ADODB.Connection)

' **********************************************************************
' ** Date:          1/3/2019
' ** Developer:     Ray Mills
' ** Purpose        Creates Customers in our Access Credit Card Database
' **                Demonstrates VBA, SQL, Connection strings, ADODB
' **                connections
' ** Note: add the following references (choose Tools then References
' **       from the VBE menu)
' ** 1)Microsoft Activex Data Objects 6.1 Library
' ** 2)Microsoft Activex Data Objects Recordset 6.0 Library
' **********************************************************************



Dim sSQL As String
Dim x As Long, y As Long, z As Double, i As Integer, j As Integer
Dim sFName As String, sLName As String, sMI As String
Dim sStrAdd As String, sCity As String, sState As String, sZpCd As Long
Dim dtCreate As Date, sAccttype As String
Dim lAcctNb As Double, lCustNb As Double
Dim sCardNb As String


'***************** Build a Customer Name *******************************

' first let's randomly decide male or female, the first and last name...

j = random_num(2)
y = random_num(1000)
z = random_num(400)

sFName = UCase(Sheets("Database Building Blocks").Cells(y + 6, j))
sLName = UCase(Sheets("Database Building Blocks").Cells(z + 6, 3))

' only 80% of our customers have middle initials  ...
i = random_num(10)
If i <= 8 Then
y = random_num(1000)
sMI = UCase(Left(Sheets("Database Building Blocks").Cells(y + 6, j), 1))
End If

'***************** Build a Customer Address *******************************
y = random_num(400)
z = random_num(41272)
sStrAdd = i & " " & Sheets("Database Building Blocks").Cells(y + 6, 4)
sCity = Sheets("Database Building Blocks").Cells(z + 6, 6)
sZpCd = Sheets("Database Building Blocks").Cells(z + 6, 5)
sState = Sheets("Database Building Blocks").Cells(z + 6, 7

'**************************************************************************
'Build a creation date: start at 1970 and add a random number of days..
i = random_num(14152)
dtCreate = DateAdd("D", i, #1/1/1980#)

' create an acct type P = personal, B = business (4% are B)...
i = random_num(100)
If i <= 94 Then
sAcctype = "P"
Else
sAcctype = "B"
End If

' build a customer and acct numbers (start with a creation date component ...
p = random_num_range(999999, 100000)
lAcctNb = Val(Str(p) & Format(dtCreate, "MMYY"))
p = random_num_range(999999, 100000)
lCustNb = Val(Str(p) & Format(dtCreate, "MMYY"))


' build the 16-digit card ...
p = random_num_range(999999999999#, 100000000000#)
sMisc = Val(Str(p) & Format(dtCreate, "MMYY"))
lCardNb = sMisc

sSQL = "INSERT INTO CUSTOMER (Creation_Date,Card_Nb,Acct_Nb,Cust_Nb, First_Name," & _
Middle_Intial, Last_Name, Street_Address,City, State, Zipcode, Cust_Type)  & _
Values(# & dtCreate & "#" & _
,' & Format(lCardNb, "####") & "'" & _
,  & lAcctNb & _
,  & lCustNb & _
, ' & sFName & "'" & _
, ' & sMI & "'" & _
, ' & sLName & "'" & _
, ' & sStrAdd & "'" & _
, ' & sCity & "'" & _
, ' & sState & "'" & _
, ' & sZpCd & "'" & _
, ' & sAcctype & "');"

' execute the sql ...
oConn.Execute sSQL
End Sub
Build a sample database
To tie our program altogether, I’ve added a form to control the execution of our program. It has:
a checkbox named cbCreateTables
a combo box named cbNoofCust
a static instruction label
a label called lblStatus
and two buttons
The code for the form is included below
'Notes: There are 4 components to the forms code:
'UserForm_Activate() loads the combo box when the form is initialized. 
'cbquit_Click() hides the form
'btn_Proceed_Click() Connects to the Access database and calls the 
'Create_A_Customer() sub routine as many as times the user indicated
'in the combobox.
'finally, the form is loaded when the user clicks on 
'the button on the Database Building Blocks worksheet  
this code, located in the BAS_Tools module, displays the form:

Sub call_frmBD()
frmBD.Show
End Sub



Private Sub btnProceed_Click()

Dim x As Long
Dim oConn As New ADODB.Connection
Dim sConn As String
Dim sAccessDB As String


' if checkbox checked create a database ...

If cbCreateTables = True Then
Call Build_a_database
Else
End If

'capture accessdb path and filename ...
sAccessDB = Sheets("Database Building Blocks").Range("E1").Value

' connect to the Access Database ...
sConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sAccessDB & _
;Persist Security Info=False;


' lets create the connection to our Access Database ...
oConn.Open sConn

'now lets create as many customers as dictated by our combobox value.. 
lblStatus.Visible = True
     For x = 1 To cbNoofCust.Value
     lblStatus.Caption = "Building Customer" & x
     frmBD.Repaint
     Call Create_A_Customer(oConn)
     DoEvents
Next x

lblStatus.Visible = True
frmBD.Hide

'clean up ...
oConn.Close
oConn = Nothing

MsgBox "Complete", vbInformation, "Employee Creation Complete"

End Sub

Private Sub cbquit_Click()

' hide the form ..
frmBD.Hide

End Sub

Private Sub UserForm_Activate()

'load the combobox
Dim x As Long

For x = 500 To 5000 Step 500
cbNoofCust.AddItem x
Next x

cbNoofCust.AddItem 10000
cbNoofCust.AddItem 15000
cbNoofCust.AddItem 20000
cbNoofCust.AddItem 25000
cbNoofCust.AddItem 30000
cbNoofCust.Value = 40000
cbNoofCust.Value = 50000

End Sub
Image

Build Any Size Customer Database Using Excel VBA

This tool will allow you to create any size sample customer database.  Simply just choose the number of customers and let the tool do the rest.

Why not share your experience using this tool.  Where you able to use and or improve it?
please. send your comments and suggestions here..

If you find the code useful and would like to see this site continue you can:
Buy me a cup of coffee

Raymond Mills MBA, MS
Raymond Mills, M.B.A., M.S.  has spent over 20 years of his career as an Accountant, Investment Bank, and Credit Card Technical Auditor/ Data Analyst.  His specialty was using Excel to get Big Databases including Teradata, Oracle,  Sequel Server, and Sybase to give up their secrets.
Ray has said, “I love nothing better than using VBA to unleash the power of Microsoft Office.” You can contact Ray @ 484 574-3190 or by emailing him Here

If you have a challenge with Excel, Access, or Word and would like to speak with Ray,   You can get his contact details by clicking here: Contact Me

Comments are closed.