Lookup MDB data when access not installed

  • Thread starter Thread starter Jeremyb
  • Start date Start date
J

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
 
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
 
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
 
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.
 
Back
Top