Hi there,
I’m working on a project, and although my advice was to use a database instead, the management insisted it has to be build in Excel (please don’t let me explain why
).
So, I thought using the listobject for data storage, which of course comes with some limitations.
Now I managed to create a workaround for all the limitations I encountered, but somehow it keeps nibbling that I’m overlooking the obvious. If not, then I guess this post will be a nice resource for others who encounter the same issues.
For explanation, I need to be able Selecting, Updating, Inserting and Deleting records just as normally performed in a database environment using ADODB.
The first two issues were, Listobjects can’t be referenced by name in a SQL statement and secondly ListObjects can’t be reference by name at all as they reside in a worksheet and not the entire workbook.
To overcome this I created two functions, one to get the Range Address of a ListObject and one to set a Listobject only by using the name of the listobject.
With the first function I was able to create a SQL statement pointing to the desired table.
Then I discovered that Selecting, Inserting and Updating was no problem, but Deleting results in an error stating the installed ISAM doesn’t support Deleting from dynamic ranges.
This resulted in a third function which uses the above mentioned function GetTableList:
Now what I'm doing is that I use an Update statement to bookmark the records that need to be deleted and than run the function DeleteFromTable to perform the actual deletion.
See code for the examples:
Does anyone know if this is really the right way to go or am I missing something here?
I’m working on a project, and although my advice was to use a database instead, the management insisted it has to be build in Excel (please don’t let me explain why
).So, I thought using the listobject for data storage, which of course comes with some limitations.
Now I managed to create a workaround for all the limitations I encountered, but somehow it keeps nibbling that I’m overlooking the obvious. If not, then I guess this post will be a nice resource for others who encounter the same issues.
For explanation, I need to be able Selecting, Updating, Inserting and Deleting records just as normally performed in a database environment using ADODB.
The first two issues were, Listobjects can’t be referenced by name in a SQL statement and secondly ListObjects can’t be reference by name at all as they reside in a worksheet and not the entire workbook.
To overcome this I created two functions, one to get the Range Address of a ListObject and one to set a Listobject only by using the name of the listobject.
Code:
Public Function GetRange(ByVal sTableName As String) As String
Dim oListObject As ListObject
Dim wb As Workbook
Dim ws As Worksheet
Set wb = ThisWorkbook
For Each ws In wb.Sheets
For Each oListObject In ws.ListObjects
If oListObject.Name = sTableName Then
GetRange = "[" & ws.Name & "$" & Replace(oListObject.Range.Address, "$", "") & "]"
Exit Function
End If
Next oListObject
Next ws
End Function
Code:
Public Function GetTableList(ByVal sTableName As String) As ListObject
Dim oListObject As ListObject
Dim wb As Workbook
Dim ws As Worksheet
Set wb = ThisWorkbook
For Each ws In wb.Sheets
For Each oListObject In ws.ListObjects
If oListObject.Name = sTableName Then
Set GetTableList = oListObject
Exit Function
End If
Next oListObject
Next ws
End FunctionWith the first function I was able to create a SQL statement pointing to the desired table.
Then I discovered that Selecting, Inserting and Updating was no problem, but Deleting results in an error stating the installed ISAM doesn’t support Deleting from dynamic ranges.
This resulted in a third function which uses the above mentioned function GetTableList:
Code:
Public Function DeleteFromTable(ByVal sTableName As String, _
ByVal sFldName As String, _
ByVal vDelValue As Variant) As Boolean
On Error GoTo err_DeleteFromTable
Dim x As Long
With GetTableList(sTableName)
For x = .ListRows.Count To 1 Step -1
If .ListRows(x).Range.Columns(.ListColumns(sFldName).Index) = vDelValue Then
.ListRows(x).Delete
End If
Next x
End With
DeleteFromTable = True
Exit Function
err_DeleteFromTable:
DeleteFromTable = False
MsgBox Err.Description
End FunctionNow what I'm doing is that I use an Update statement to bookmark the records that need to be deleted and than run the function DeleteFromTable to perform the actual deletion.
See code for the examples:
Code:
Public Sub PlayWithADO()
Dim sFullName As String
Dim sSQL As String
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim wb As Workbook
Dim sTableName As String
Set wb = ThisWorkbook
sFullName = wb.FullName
sTableName = "tTest"
With cn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Properties("Extended Properties").Value = "Excel 12.0 Macro;HDR=YES"
.Open sFullName
End With
'Select records
sSQL = "Select Name, SomeValue from " & GetRange(sTableName) & ""
Set rs = cn.Execute(sSQL)
'Loop records
With rs
.MoveFirst
Do Until .EOF
Debug.Print .Fields(0).Value; .Fields(1).Value
.MoveNext
Loop
.Close
End With
'Or insert records
sSQL = "Insert Into " & GetRange(sTableName) & " (Name, SomeValue, SomeOtherValue) Values ('New Name', 20, 5)"
cn.Execute sSQL
'Or update records
sSQL = "Update " & GetRange(sTableName) & "Set Name = 'FatBoy' Where Name = 'New Name'"
cn.Execute sSQL
'And delete records
'First bookmark records to delete using update statement
sSQL = "Update " & GetRange(sTableName) & "Set Name = 'Delete' Where SomeValue = 20 And SomeOtherValue = 5"
cn.Execute sSQL
DeleteFromTable sTableName, "Name", "Delete"
cn.Close
End SubDoes anyone know if this is really the right way to go or am I missing something here?
