I have a database that presents the user with a form into which they enter an order number. This order number is used in a query to find all the related work orders, after which the form's vba code places those work order identifiers into a temporary table. Next, the code opens two reports which are based on queries which use the work order numbers in the temporary table plus data from some linked tables to generate data for the reports. When I use this system logged into my (administrator) account, everything is fine. However, when run from a non-admin account, it fails with an ODBC error citing insufficient permissions for that user account. Unchecking "use trusted account" and inputting the SQL server admin credentials does not work either. The weird thing is, if I manually enter some work order numbers into the teporary table and manually open the reports, everything works fine. The problem only comes up when the vba code tries to a) run any of the supporting queries via a QueryDef object or b) open the reports in preview mode. I tried to circumvent the first by using a DSN-less connection from within the vba code to run the queries against the SQL server directly, and that worked. However, the vba code doesn't want to open the report in preview mode and raises the same error. Is there something I need to do in order to give those querydefs the correct level of rights to perform the required actions?

Thanks in advance

J,

In theory the Linked tables should appear as if they were resident Access Tables.

Since they don't, how does your VBA code reference them?

The fact that it is a DSN-less connection shouldn't matter.

Need to see your VBA code.

Wayne

do you "create" the temporary table, or just add to an existing temporary table - if the former, then it might still be a folder permissions thing.

step through the code with a breakpoint - then you can see exactly which line it doesnt like/causes the problem

Sorry, I wrote my original post on the train and didn't have access to my files.

Here is the exact error message:

Connection failed:
SQLState: '28000'
SQL Server Error: 18456
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '3-FORM\nonadminusername'.

I press ok, then a dialog box comes up allowing me to enter alternate credentials. I then press cancel and get the end/debug dialog box. I click debug and it takes me to the highlighted code below:


...
If Not cutCount <= 0 Then
Dim wcoRs As New ADODB.Recordset
wcoRs.Open "SELECT WORKORDER_BASE_ID FROM TFORM_CUT_REPORT WHERE WORKORDER_BASE_ID Like '&#37;" & Me.CO_NUM & "%';", conn, adOpenStatic, , adCmdText

wcoRs.MoveFirst
Do Until wcoRs.EOF
CurrentDb.Execute ("insert into CUT_WCO values ('" & wcoRs.Fields("WORKORDER_BASE_ID") & "');")
wcoRs.MoveNext
Loop

wcoRs.Close

DoCmd.OpenReport "Cut Report Summary", acViewPreview
DoCmd.OpenReport "Cut Report", acViewPreview

If Me.PrintCheckBox.Value = True Then
DoCmd.SelectObject acReport, "Cut Report Summary", False
DoCmd.PrintOut acPrintAll
DoCmd.Close acReport, "Cut Report Summary", acSaveNo

DoCmd.SelectObject acReport, "Cut Report", False
DoCmd.PrintOut acPrintAll
DoCmd.Close acReport, "Cut Report", acSaveNo
End If

CurrentDb.Execute ("delete from CUT_WCO")
...
As I mentioned above, if I put work order numbers into the CUT_WCO table manually and then manually open the cut report summary, it works fine. It's only when I try to open it with VBA that it gives me the error message above. Also note the first sql select query. I originally created a query in access which does the same thing and then used a querydef to run it from within this code snippet. When run without network admin credentials it would result in the same error message. I changed it to the above as a workaround, which works fine.

Addition:
No, the temporary table exists within access only and is never created or destroyed. Also, the reason it's stopping on the line I highlighted is because the report is based on one of the queries I created in Access and it's the same problem, I'm sure.

Thanks

insteaqd of opening the report put

docmd.opentable "CUT_WCO"
docmd.openquery "then put the source query for the report"

to see if a) the table was populated as you expected
and b) to see if the report's query opens properly

Thanks for the help, everyone. I was able to fix this by re-linking the tables while logged in as the non-admin user. I had failed to mention that this database was originally created using 2003 and I'm trying to get it working with 2007 - I'm sure it was because of this.

Thanks