Docmd.Openquery requires more than read/write?

[ahjeck]

Hi,

I have a client-server setup with a SQL Server 2005 backend, with an Access 2003 client front-end forms. On this front-end I have several pass-through queries which basically transfer some records from another SQL Server 2005 database to this database, on different servers.

Through VBA, i have some code that uses 'DoCmd.Openquery "<append queries>" that basically runs an append query that does the transfer.

I have found out that it does not work when a user only has db_datareader and db_datawriter as their only permissions in SQL Server. When this is set and the user attempts to run the query, I get an error indicating that he cannot append.

But when I add the role db_ddladmin, they can run the query fine.

Basically, my question is...why does the DoCmd.OpenQuery require db_ddladmin permissions from SQL Server? The append query is nothing more than an INSERT...SELECT statement copying files from two different servers.

Thanks in advance...

 

[SQL_Hell]

Yes you are right you should only need datareader and datawriter permissions for this, ddl_admin gives you permissions to create stored procs, alter assemblies ect ect.

Can you use docmd.runsql instead?

 

[ahjeck]

Yes you are right you should only need datareader and datawriter permissions for this, ddl_admin gives you permissions to create stored procs, alter assemblies ect ect.

Can you use docmd.runsql instead?

Thanks for the reply. I'm unsure whether DoCmd.Runsql will work in this case because I'm pulling data from one server's db to another server's db.

So the sql statement doesn't seem to execute...but i don't get an error message either...

Dim strSQL as string    
strSQL = "INSERT INTO tblEmployeeInfo (ID, DOB, SSN, FirstName, LastName, MiddleName, Gender)" & _
             "SELECT ID, DOB, SSN, FirstName, LastName, MiddleName, Gender FROM Server2.Database2.dbo.EmployeeInfo;"
     
DoCmd.RunSQL strsql

 

[ahjeck]

I also did a test run w/ using the SQL statements gathered from the pass-through query. It runs, but I get the same error message if I don't have db_ddladmin permissions.

DoCmd.RunSQL "INSERT INTO tblEmployeeInfo SELECT qryServerEmployeeInfoNew.* FROM qryServerEmployeeInfoNew;"

 

[SQL_Hell]

It is weird that it works with ddl_admin

This statement below, you posted earlier. Is dbo.EmployeeInfo defintely a table and not a view?

Dim strSQL as string    
strSQL = "INSERT INTO tblEmployeeInfo (ID, DOB, SSN, FirstName, LastName, MiddleName, Gender)" & _
             "SELECT ID, DOB, SSN, FirstName, LastName, MiddleName, Gender FROM Server2.Database2.dbo.EmployeeInfo;"
     
DoCmd.RunSQL strsql

I am going to try this out once I get back to work tomorrow, as I haven't tried to access data on a remote server through Access VBA before.

 

[ahjeck]

Hi, yes there are no views used in this database. It's all tables. Thanks.