Hi Fergal,
I've gone through this and ripped out all the centralized error handler components, setting it back to locally handled. The error handling isn't robust, by any means, and the code is a work in progress. Original intention is to allow it to be robust enough that it can connect to Access (mdb or accdb) files, SQL Server, or SQL express. I've also tried to make it as easy as possible to throw basic SQL in there.
To use this, import the project into your module. You'd then call the routine with something like this:
Code:
Sub Retrieve()
Dim Db As New clsDBLink
Dim rcdSet As Recordset
With Db
.dbType = dbSQL
.dbServer = "//servername"
End With
Set rcdSet = Db.sqlSelectToRecordset("Column Name", "Table Name")
End Sub
You will need to set a reference (Tools|References) to the ActiveX Data Objects 2.x Library before you can run the code. The above sample will return whatever query you put in to a recordset that you can parse.
Other methods in the class are:
sqlDeleteRecord(FromTable As String, Where As String) As Boolean
Returns TRUE if record is successfully deleted
Code:
bSuccess = sqlDeleteRecord("Employees","Name='Joe Bloggs'")
If bSuccess Then msgbox("Employee deleted!")
sqlInsertRecord(ToTable As String, Columns As String, Values As String) As Boolean
Returns TRUE if record successfully insert
sqlSelectToArray(sColumns As String, FromTable As String, Optional Where As String, Optional OrderBy As String) As Variant
Returns an array of data or an array of with 0,0 elements if no data retreived
sqlSelectToRange(clTrgt As Range, Columns As String, FromTable As String, Optional Where As String, Optional OrderBy As String) As Boolean
Returns the recordset directly to a range and returns TRUE if successful, FALSE if not
sqlSelectToRecordset(Columns As String, FromTable As String,Optional Where As String, Optional OrderBy As String) As ADODB.Recordset
Returns the recordset to an adodb recordset
sqlUpdateRecord(ToTable As String, SetColumnTo As String, Where As String) As Boolean
Updates a recordset, returns TRUE if successful, FALSE if not
sqlUserDefined(sSQL As String) As Boolean
Put in whatever SQL query you want. Returns TRUE if executed succesfully, FALSE if not.
I use those TRUE/FALSE states to toggle:
TransactionProcessing(tpState)
Between tpBegin, tpCommit and tpRollBack
Hope this helps,
Bookmarks