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

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.

116 Downloads

 

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
4.  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 a first name from column A or B.
3. It Randomly selects a 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 all together, I’ve added a form to control our programs execution. It has:
a checkbox named cbCreateTables
a combobox 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

Pease feel free to offer suggestions, comments on my Build a Sample Database tool or to contact me about how I might help you with your next project, Click here

Raymond Mills MBA, MS

Raymond Mills, M.B.A., M.S.  has spent over 20 years of his career as 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.” 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 

Excel and VBA Craftsman offers experienced VBA Development: Complete GUI Driven Applications, Automated Reconciliations, VBA ODBC Database Data Mining, VBA Excel, Access, Outlook and Word Customization. VBA Custom Functions, VBA Concept Assessment, VBA integration of MS office applications into powerful single seamless solutions, Excel Help, Automate Excel, Excel Automation