Lookup MDB data when access not installed

[Jeremyb]

I have written a form and work flow that looks up names in a MDB file to
populate a combo box.
This works fine as long as I have Access installed on PC.
But most users who'll use the form don't have MS Access installed.

The code I am using is:

Function FillComboBox()

Set objAccess = Item.Application.CreateObject("Access.Application")
strAccessDir = "H:\My Documents\"
strDBName = strAccessDir & "Northwind.mdb"
'MsgBox "DBName: " & strDBName
objAccess.Quit


Set dao = Application.CreateObject("DAO.DBEngine.36")
Set wks = dao.Workspaces(0)
Set db = wks.OpenDatabase(strDBName)


Set rst = db.OpenRecordset("Employee")
Set ctl = Item.GetInspector.ModifiedFormPages("RAR
Form").Controls("RARRequestFor")

ctl.ColumnCount = 1
ctl.ColumnWidths = "75 pt;"
CategoryArray(99, 2) = rst.GetRows(200)
ctl.Column() = CategoryArray(99, 2)

It errors at the Set objAccess line, because Access is not available.
How can I code this so that MS Access is not required.

I am a beginner with coding and need a little help to just get this correct.
I am so close to getting this to work.

Thanks

Jeremy

 

[Ken Slovak - [MVP - Outlook]]

The best you can do is fail gracefully if Access isn't installed since the
control is being filled from the database.

Use something like this:

On Error Resume Next

Set objAccess = Item.Application.CreateObject("Access.Application")
If objAccess Is Nothing Then
Err.Clear
MsgBox "Access not installed"
End If

 

[Jeremyb]

Ken

Thanks. But is there not some other type of data connection that could be
used.

I could use SQL as all have access to SQL server, but may need help with
coding here

Thanks

 

[Ken Slovak - [MVP - Outlook]]

If everyone has access to the SQL server, yes you can use that as a data
source. For how to access that SQL server you'd need to know how to access
it and write that code. For that you'd be best off posting in a SQL server
group.