user form that retrieves data from Microsoft Access query

[Eric]

I created a word document that displays a userform when the template is
opened, when you click on the ok button on the userform the template should
display data that comes from a microsoft access query. The thing is that this
works perfectly for me with my non-administrators account. The problem is
that whenever another user is trying to open this template no data is being
displayed. I checked the permissions on the folder where the template is
located and that looks fine.

Anyone has any idea what I might be doing wrong here?

Thanks in advance.

 

[Gordon Bentley-Mix]

Where is the database, and does everybody have permissions to it?

Might also help if we could have a look at the code you're using for the
Access query.

 

[Eric]

This is the code behind the userform used
to retrieve the data from the access accdb:

Sub Generatecable()
'
' Generate Cable Macro

Dim DB As Database, Pay As Recordset, Vou As Recordset, SumPay As
Recordset
Set DB = OpenDatabase("q:\jas\fmc\RentalPayments\RentalPayments.accdb")
Set Pay = DB.OpenRecordset("SELECT * FROM qryCableDataLastQuarter",
dbOpenSnapshot)
Set Vou = DB.OpenRecordset("SELECT * FROM qryCableDataVoucherAmounts",
dbOpenSnapshot)
Set SumPay = DB.OpenRecordset("SELECT * FROM qryCableDataTotalAmount",
dbOpenSnapshot)

If Pay.BOF And Pay.EOF Then
MsgBox ("No data found.")
GoTo exit_sub
End If

Dim PayRecs As Long, VouRecs As Long
Pay.MoveLast
PayRecs = Pay.RecordCount
Vou.MoveLast
VouRecs = Vou.RecordCount


Note: none of my users get to see the USERFORM upon opening the template.
They all have sufficient permissions to the access database.

Thanks

 

[Eric]

I managed so that all users see the userform but when generating the document
and trying to open the database I get the following error message: USER
DEFINED TYPE IS NOT DEFINED. The program stops at Dim db as Database

 

[Gordon Bentley-Mix]

Do you have a reference to the DAO Object Library? Does this library exist
on the users' machines? Try putting each variable in a separate Dim
statement and qualifying each with "DAO.". Then see which variable - if
any - is throwing the error.

 

[Eric]

I managed to set a reference to the DAO Object library but now I'm getting an
error that says: UNRECOGNIZED DATABASE FORMAT when the program hits the line
: OpenDatabase("q:\jas\fmc\RentalPayments\RentalPayments.accdb")

(Thanks for all your help!)

Eric

 

[Eric]

I created an Access 2003 version that holds the tables and queries and when
referring to the .MDB instead of the .ACCDB the template works fine, I can
connect to the database and retrieve the data. I would like to know why I
cannot connect to the ACCDB.

Regards,

Eric

 

[Peter Jamieson]

There are (at least) two DAO libraries - the old one is called
"Microsoft DAO 3.6 Object Library" and is implemented by dao360.dll. In
Access 2007 the database negine was substantially modified and renamed
from Jet to ACE. The new one is called "Microsoft Office 12.0 Access
database engine Object" and is implemented by ACEDAO.DLL. You probably
need the /newer/ one.

Peter Jamieson

http://tips.pjmsn.me.uk

 

[Eric]

Thanks Peter, that did it, I needed to set regerence to Microsoft Office 12.0
Access database engine. many thanks!!!