ADODB Connection Permissions Issue

[scottetombleson]

I have a db that I recently secured and I am having problems working with a recordset that worked previously.

Basically, I'm grabbing records from an attached MySQL db, Creating a table from them, modifying the table, then trying to open a recordset to check to make sure related records exist in another table.

The offending code:

            Dim curConn As New ADODB.Connection
            Dim rst As New ADODB.Recordset
            Dim CurDB As Database
            Dim FIDP As String
            Dim FName As String
            Dim Lname As String
            
        
            Set CurDB = CurrentDb()
            Set curConn = New ADODB.Connection
            
            With curConn
                .Provider = "Microsoft.Jet.OLEDB.4.0;"
                .ConnectionString = "data source= " & CurDB.Name
                .Open
            End With
            
            '___________________________________________________________
            'open the tempMaster_F table created in the lookup form
            Set rst = New ADODB.Recordset
            rst.CursorType = adOpenDynamic
            rst.LockType = adLockOptimistic
            rst.Open "tempMaster_P", curConn, , , adCmdTable
            rst.MoveFirst
            
            Do Until rst.EOF
            
            '___________________________________________________________
            'set varibles to fields in table
            
            FIDP = rst!FID
            
            
            '-------------------------------------------------------------
            'Check Family table to see if Master_F FID exists in it
            'if not then it was addes by online reg and needs to be added to family table
            
            If IsNull(DLookup("[FIRSTNAME]", "[tempMaster_F]", "[FID] = " & FIDP)) Then
            
            FName = rst!FirstName
            Lname = rst!LastName
            
            
                Dim Response As Integer
                
                Response = MsgBox(FName & " " & Lname & " does not have a matching Family Record in the Online Registration Database.  Please ensure that the Family record is marked with a status of 'A' and run this process again.", vbCritical, "No Matching Family Record!")
                    DoCmd.Close acForm, Me.Name
                 rst.Close
                 Exit Sub
            End If
            rst.MoveNext

            Loop

            rst.Close

The error that i get is

Run-time error '-2147467259 (80004005)': You do not have the necessary permissions to use the 'C:\Database\SIC.mdb' Object. Have your system administrator or the person who created this object establish the permissions for you.


As you may have suspected I am in fact the administrator and I have given myself every permission that I can think of.

Also in Debugging .Open is highlighted.

Thanks in advance for any help as you all always do!

Scott

 

[RoyVidar]

You are kind of creating an external connection to the database you are working with. I don't know much about the security stuff, but couldn't you do just:

[tt]Set rst = New ADODB.Recordset
with rst
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.activeconnection = currentproject.connection
.Open "tempMaster_P", , , , adCmdTable
.MoveFirst
....[/tt]

Roy-Vidar