Using SQL in VBA

There are two files available:

The files demonstrates how to use SQL in Excel to:

  • create a database,
  • create a table and add data to it,
  • select data from a table,
  • delete a table,
  • delete a database.
Below is a short overview of the file and some of the code.
 
I have tried to make the code simple so you can follow what I am trying to achieve and consequently I have restricted error checking to a bare minimum. If you choose to use this code in your own application, please remember that more error checking will probably be required. 
 
Where ever possible I have used SQL code because it is widely known and more likely to make sense to anyone who has had database experience and now wants to use Excel as a front-end to an Access back-end. There are two cases, in this demonstration, where SQL code can't be used.
 
Below are lists of SQL data definition and manipulation commands.
Those in BLUE are covered in the workbook.
 
Data Definition

CREATE TABLE
CREATE INDEX
ALTER TABLE
CONSTRAINT
DROP 
Data Manipulation

SELECT
SELECT … INTO
INSERT
INSERT … INTO
UPDATE
DELETE
INNER JOIN
LEFT JOIN, RIGHT JOIN
PARAMETERS
UNION

CREATE TABLE
The first task is to create a database. I use two routines: "CreateADatabase" to do the donkey work, and "CreateDb" as a cover which can be used anywhere in your code. 
 
(Please note: these examples are from the DAO workbook)
'' ***************************************************************************
'' Purpose : Cover to create an Access database
'' Written : 20-Oct-1999 by Andy Wiggins - Byg Software Ltd
''
Sub CreateDb()
CreateADatabase ThisWorkbook.Path & "\" & ctDb
End Sub
 
'' ***************************************************************************
'' Purpose : Create an Access database
'' Written : 03-Jul-1997 by Andy Wiggins - Byg Software Ltd
''
Sub CreateADatabase(vtDbName$)
Dim dbs As Database
Dim vtMessage$
On Error GoTo ErrorHandler

'' See "CreateDatabase Method" in help for available options
Set dbs = Workspaces(0).CreateDatabase(vtDbName & ".mdb", _
dbLangGeneral, dbVersion30) 'dbVersion20) for Jet v2
Set dbs = Nothing
Exit Sub

ErrorHandler:

vtMessage = "Database creation error"
vtMessage = vtMessage & _
Chr(10) & _
Chr(10) & "Error Number: " & Err & _
Chr(10) & "Error Description: " & Error()

MsgBox vtMessage, vbInformation, ctByg
End Sub
 
INSERT
This routine does a lot of work. It opens the database so it is ready to receive new data, creates a new table, copies the data from an Excel range into the table, and closes the database.
'' ***************************************************************************
'' Purpose : Create a table from a range on a sheet
'' Written : 19-Oct-1999 by Andy Wiggins - Byg Software Ltd
''
Sub CreateTableAndAddData()
Dim dbs As Database
Dim viCols%
Dim viRows%
Dim viCount%
Dim viRcount%
Dim vtWrapChar$
Dim vtSql$
Dim vtMessage$

On Error GoTo ErrorHandler


ThisWorkbook.Activate

'' - - - - - - - - - - - - - - - - - - - - - - - - - - - -
''Open the database
Set dbs = OpenDatabase(ThisWorkbook.Path & "\" & ctDb)

'' - - - - - - - - - - - - - - - - - - - - - - - - - - - -
''Go to the top left corner of the range
Application.Goto reference:=Range("rtlData")

''Get some info about the range
With ActiveCell.CurrentRegion
viCols = .Columns.Count
viRows = .Rows.Count
End With
 
'' - - - - - - - - - - - - - - - - - - - - - - - - - - - -
''Ensure the target table does not exist - careful, there's no recovery
''if you delete a table that you wanted.
On Error Resume Next
vtSql = ""
vtSql = vtSql & " DROP TABLE " & ctSheet
dbs.Execute vtSql
On Error GoTo ErrorHandler
 
'' - - - - - - - - - - - - - - - - - - - - - - - - - - - -
''Create the table
''Go to the top left cell in the current range
ActiveCell.CurrentRegion.Cells(1, 1).Select

vtSql = ""
vtSql = vtSql & " CREATE TABLE " & ctSheet & " ("
''Loop around each column to create the SQL code
''Column names must not contain spaces
With ActiveCell.CurrentRegion
For viCount = 1 To viCols
vtSql = vtSql & .Cells(1, viCount) & "x " & _
fGetCellFormat(.Cells(2, viCount))
If viCount <> viCols Then
vtSql = vtSql & ", "
Else
vtSql = vtSql & ")"
End If
Next
End With

''This has created the following SQL code ...

''CREATE TABLE DataSource
'' (Staff_Nox NUMBER,
'' Salx CURRENCY,
'' Namex TEXT,
'' Boolyx BIT,
'' Regionx NUMBER,
'' Datex DATETIME)

''.. which is exexcuted in the database
dbs.Execute vtSql
 
''Note: I have concatenated an "x" to the field name to try
'' and avoid reserved word conflicts in Access, e.g., if
'' a column was called "Date"
 
'' - - - - - - - - - - - - - - - - - - - - - - - - - - - -
''Insert data into the table
With ActiveCell.CurrentRegion
For viRcount = 2 To viRows

vtSql = ""
vtSql = vtSql & " INSERT INTO " & ctSheet
vtSql = vtSql & " VALUES ("
For viCount = 1 To viCols
Select Case fGetCellFormat(.Cells(2, viCount))
Case "TEXT"
vtWrapChar = """"
Case "DATETIME"
vtWrapChar = "#"
Case Else
vtWrapChar = ""
End Select

vtSql = vtSql & vtWrapChar & _
.Cells(viRcount, viCount) & vtWrapChar

If viCount <> viCols Then
vtSql = vtSql & ","
Else
vtSql = vtSql & ")"
End If
Next
dbs.Execute vtSql
Next
End With
 
'' - - - - - - - - - - - - - - - - - - - - - - - - - - - -
''Close and tidy up
lbTidy:
dbs.Close
Set dbs = Nothing
 
Exit Sub

 

'' - - - - - - - - - - - - - - - - - - - - - - - - - - - -
ErrorHandler:

vtMessage = "Table and data creation error"
vtMessage = vtMessage & _
Chr(10) & _
Chr(10) & "Error Number: " & Err & _
Chr(10) & "Error Description: " & Error()

MsgBox vtMessage, vbInformation, ctByg
Resume lbTidy
 

End Sub

     
SELECT
'' ***************************************************************************
'' Purpose : Select records from a table
'' Written : 19-Oct-1999 by Andy Wiggins - Byg Software Ltd
''
Sub SelectAndReturnRecords()
Dim dbs As Database
Dim rs As Recordset
Dim vtSql$
Dim numberOfRows

On Error GoTo ErrorHandler

ThisWorkbook.Activate

'' - - - - - - - - - - - - - - - - - - - - - - - - - - - -
''Open the database
Set dbs = OpenDatabase(ThisWorkbook.Path & "\" & ctDb)

'' - - - - - - - - - - - - - - - - - - - - - - - - - - - -
''The Execute method is valid only with action queries.
''Select queries contain a SELECT statement and can return records - action
''queries do not.
vtSql = ""
vtSql = vtSql & " SELECT * "
vtSql = vtSql & " FROM " & ctSheet
vtSql = vtSql & " WHERE Namex Like 'R*'"
Set rs = dbs.OpenRecordset(vtSql)

 

With ThisWorkbook.Sheets(ctTarget)
With .Cells(1, 1)
.CurrentRegion.Clear
''Copies the contents of a DAO Recordset object
''onto a worksheet
numberOfRows = .CopyFromRecordset(rs)
End With
End With

'' - - - - - - - - - - - - - - - - - - - - - - - - - - - -
''Close and tidy up
lbTidy:
dbs.Close

Set dbs = Nothing
Set rs = Nothing

 

Exit Sub

'' - - - - - - - - - - - - - - - - - - - - - - - - - - - -
ErrorHandler:
Dim vtMessage$

vtMessage = ""
vtMessage = vtMessage & _
Chr(10) & _
Chr(10) & "Error Number: " & Err & _
Chr(10) & "Error Description: " & Error()

MsgBox vtMessage, vbInformation, ctByg
Resume lbTidy
End Sub
     

Published: 2003
Last edited: 11-Jun-2005 23:52