Solved: Access error no read permission

[Danny]

I am trying to return a recordset from access to excel with an ado connection. The VBA code works fine on my home computer, but at my office the code returns:

run-time error '-2147217911 (80040e09)': Record(s) cannot be read; no read permission on TableName.

The database does not have a password, the only difference is that a logon password is required to use access (before any database is opened) . Could this possibly be the problem? If so, is there a way to work around this?

Here is the code:

[vba]
Const ConnectionString As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;" + _
"Data Source=C:\Users\Owner\Desktop\ADO.mdb;" + _
"Persist Security Info=False"
Dim Connection As ADODB.Connection
Set Connection = New ADODB.Connection
Connection.ConnectionString = ConnectionString
Connection.Open
Dim Recordset As Recordset
Dim SQL As String
Dim RowsAffected As Long

SQL = "SELECT Resolution.CutOff, Resolution.LoanNumber, LoanToInvestor.Investor, ServNbrToAccount.[P&IAccount], ResolutionDetail.DueDate, ResolutionDetail.RPayment, ResolutionDetail.RRate, ResolutionDetail.RSrvRate, ResolutionDetail.RBegBal, ResolutionDetail.RCurt, ResolutionDetail.CPayment, ResolutionDetail.CRate, ResolutionDetail.CSrvRate, ResolutionDetail.CBegBal, ResolutionDetail.CCurt, Resolution.ResolutionID " & _
"FROM (ServNbrToAccount INNER JOIN ((InvestorToServNbr INNER JOIN LoanToInvestor ON InvestorToServNbr.Investor = LoanToInvestor.Investor) INNER JOIN Resolution ON LoanToInvestor.[BAC Loan] = Resolution.LoanNumber) ON ServNbrToAccount.ServNbr = InvestorToServNbr.ServNbr) INNER JOIN ResolutionDetail ON Resolution.ResolutionID = ResolutionDetail.ResolutionID " & _
"WHERE (((Resolution.ResolutionID)=2));"

'code fails here
Set Recordset = Connection.Execute(SQL, RowsAffected, CommandTypeEnum.adCmdText)

sh3.Range("A2").CopyFromRecordset Recordset
[/vba]

[OBP]

I would suggest that the logon Password to use Access is the problem.
Have you tried using Access without that Password?
What is the Point of the password anyway?

[Danny]

OBP,
Access can not be used without entering the logon ID and password. Access will ask for this no matter what database you try to open, and even if you just open access to create a new database. I don't really know the point of the password. I thought you didn't even need an access license to use ado with JET.

[OBP]

Is the logon a Systems requirement as I have never heard of Logging in to Access without a database.

[Danny]

I think it may have something to do with the User and Group Accounts in Tools>Security>User and Group Accounts.

[OBP]

Ok, but even with a Secured Database you should still be able to open Access without opening a database and not get the message. You need to Join the normal System.mdw in the Office Folder.
If your database is Secured you have to use the password.

[Danny]

As soon as i open access (not a database) i am asked login id and password. if i hit cancel the access window will stay open but the login prompt will reappear befor i am able to open any new or existing database. However once I have entered the password i can open a database, close it and then open a different database without having to re-enter a password, as long as, it is the same instance of access.
how do i go about joining the normal sytem.mdw?

[OBP]

You Join the system .MDW file using the Main Menu>Tools>Security>Workgroup Administrator
Tell it you want to Join a Workgroup then Browse to where your System.MDW is located, it is usually in Program Files>Microsoft Office>Office 10 or 12
When you find it click on it and then click OK and you will joined.

[Danny]

OBP,
I joined the system.mdw file and still returned the same error, i tried changing the settings on all the system and hidden objects with no luck. I am petty much stumped. Do I need to convert the MDB and the MDW to the 4.0 database format to use the 4.0 OLE DB Provider? I also found this through a google search and wanted to see if you may have any more ideas.
Thanks for all your help so far
[vba]
Dim tblrestrictions As String() = New String() {Nothing, Nothing, Nothing, "TABLE"}
Dim dt As DataTable = olecon.GetSchema("tables", tblrestrictions)

Note:
Restrictions are a string array in the following format:
{TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE}.
[/vba]

[Danny]

deleted

[OBP]

Sorry, I don't know anything about the 4.0 OLE DB Provider, does it have it's own Forum, they can be a good source of "Fixes".