Using DAO to search Excel database

[Roderick]

I'm now working in Windows 7 and Word 2013. This is an absolutely clean machine set up and used only for this kind of work.

I've written an Initialize procedure in a userform to go and search an Excel database file. The end result when I try to run it it says that it cannot find the .xls file.

It works fine until this line and then tells me that it is an invalid argument.


Set db = OpenDatabase(inifileloc2, False, False, "Excel 8.0")


This is the code for the procedure I'm referring to:


Private Sub UserForm_Initialize()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim iNoOfRecords As Integer

'Won't let the default button become active if the "Default is selected as the default.
If cboOfficeLocation.Value = "My Default" Then
cmdDefaultOffice.Enabled = False
cmdOK.Enabled = True
End If

VarPathLocal = Options.DefaultFilePath(wdUserTemplatesPath)

FullPath = VarPathLocal + "\" + "My Office Details.xls"

inifileloc2 = FullPath

' Open the database
cmdDefaultOffice.Enabled = False

Set db = OpenDatabase(inifileloc2, False, False, "Excel 8.0")

' Retrieve the recordset
Set rs = db.OpenRecordset("SELECT * FROM `MyOfficeRange`")

' Determine the number of retrieved records
With rs
.MoveLast
iNoOfRecords = .RecordCount
.MoveFirst
End With

...and so on through the initialisation procedure.

The References set are as follows:


Visual Basic for Applications
Microsoft Word 16.0 Object Library
OLE Automation
Microsoft Office 16 Object Library
Microsoft Forms 2.0 Object Library
Microsoft DAO 3.6 Object Library

For the life of me I cannot see where the incorrect logic is being entered!

Can anyone suggest a method that might work, please?

[gmaxey]

Roderick

DAO is some pretty old stuff and there are several reports on Google that the Jet database engines have been adversely affected by an October update.

Try ADODB. Here is an example of code I use to populate a listbox with data from and excel file in the same folder.


Sub LoadFromExcel_ADODB(ByRef oListPassed As Object, ByRef strSource As String, Optional strColWidths = "100")
'Requires reference to the "Microsoft ActiveX Data Object 2.8 Library."
Dim oConn As New ADODB.Connection
Dim oRecSet As New ADODB.Recordset
Dim strConnection As String
Dim strRange As String
Dim lngCount As Long
p_strPath = ThisDocument.Path & Application.PathSeparator
strRange = "mySSRange"
'If the source range is a worksheet use:
'strRange = strRange & "$]"
'Else
strRange = strRange & "]"

Set oConn = CreateObject("ADODB.Connection")
'Suppress first row.
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & p_strPath & "sourceSpreadSheet.xls;" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
'No suppression.
'strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & p_strPath & "sourceSpreadSheet.xlsx;" & _
"Extended Properties=""Excel 12.0 Xml;HDR=NO"";"
oConn.Open ConnectionString:=strConnection

Set oRecSet = CreateObject("ADODB.Recordset")
'Read the data from the worksheet.
oRecSet.Open "SELECT * FROM [" & strRange, oConn, 2, 1
With oRecSet
.MoveLast
'Get count.
lngCount = .RecordCount
.MoveFirst
End With
With oListPassed
'Load the records into the columns of the named list/combo box.
.ColumnCount = oRecSet.Fields.Count
.Column = oRecSet.GetRows(lngCount)
End With
'Cleanup
If oRecSet.State = 1 Then oRecSet.Close
Set oRecSet = Nothing
If oConn.State = 1 Then oConn.Close
Set oConn = Nothing
lbl_Exit:
Exit Sub
End Sub