ADO connection in Excel 2000

[Kirin]

I want to use excel as a database. I have two workbooks. One that have the vba code and registration, and one that just store the data. When I open up the excel workbook it checks the other workbook and gets the data without open the workbook.
So far so well, but i also wan't to change the data and add some more.
Here is the code i get so far:

Sub GetWorksheetData(strSourceFile As String, strSQL As String, TargetCell
As Range)

Dim cn As ADODB.Connection, rs As ADODB.Recordset, f As Integer, r As Long
If TargetCell Is Nothing Then Exit Sub
Set cn = New ADODB.Connection
On Error Resume Next
cn.Open "DRIVER={Microsoft Excel Driver (*.xls)};DriverId=790;
ReadOnly=True;" & "DBQ=" & strSourceFile & ";"
' DriverId=790: Excel 97/2000
' DriverId=22: Excel 5/95
' DriverId=278: Excel 4
' DriverId=534: Excel 3
On Error GoTo 0
If cn Is Nothing Then
MsgBox "Can't find the file!", vbExclamation, ThisWorkbook.Name
Exit Sub
End If

' open a recordset
Set rs = New ADODB.Recordset
On Error Resume Next
rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText

On Error GoTo 0
If rs Is Nothing Then
MsgBox "Can't open the file!", vbExclamation, ThisWorkbook.Name
cn.Close
Set cn = Nothing
Exit Sub
End If


TargetCell.CopyFromRecordset rs

If rs.State = adStateOpen Then
rs.Close
End If
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

Private Sub Workbook_Open()
GetWorksheetData "w:\Dokument\Test.xls", "SELECT * FROM [Ark1$];",
ThisWorkbook.Worksheets(1).Range("A1&quot
GetWorksheetData "w:\Dokument\Test.xls", "SELECT * FROM [Ark2$];",
ThisWorkbook.Worksheets(2).Range("A1&quot
End Sub


Can someone help me ?

 

[SkipVought]

Kirin,

I would caution against using Excel as a database if you are using your database as such...
1. tables with relationships,
2. performing INSERT, UPDATE and DELETE SQL

I have used Excel as a database and got snake-bit. You would be much better off to use a true database application like MS Access. You can still use Excel as a front end and access the Access data via ADO.

Now, on your code, you had an extra " in this statement...

    cn.Open "DRIVER={Microsoft Excel Driver (*.xls)};DriverId=790;ReadOnly=True;" & _
        "DBQ=" & strSourceFile & ";"

Hope this helps



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com