Problem with appending MS Excel data into Access 1

[qwerty70]

Dear All,

I have a problem appending my Excel data into our database that is stored in our server. When I hit the command
button on my Excel sheet, I get this error:

Run-time error '3033':
You do not have the necessary permissions to use the
'H:\206_PDDM\TDC_DB\TDCSK.mdb' object. Have your system
administrator or the person who created this object establish the
appropriate permissions for you.

If I press OK, the debugger stops at this line highlighted in yellow:

Set db = OpenDatabase("H:\206_PDDM\TDC_DB\TDCSK.mdb")

Please note that the folder is mapped as "H" drive.

Here's the code behind my Excel command button:

Private Sub CommandButton1_Click()

      Dim db As Database
      Dim rs As Recordset
      Dim XLTable As TableDef
      Dim strSQL As String
                 
        Dim Msg, Style, Title, Help, Ctxt, Response
        Msg = "Do you want to continue?" & vbCrLf & _
        "If Yes, the data will be added" & vbCrLf & _
        "in the TDC database and you cannot" & vbCrLf & _
        "undo the data transfer."
        Style = vbYesNo + vbInformation + vbDefaultButton2
        Title = "Upload data into TDC system"
        
        Response = MsgBox(Msg, Style, Title)

If Response = vbYes Then    ' User chose Yes.
      
          'Open the Microsoft Access database.
          Set db = OpenDatabase("H:\206_PDDM\TDC_DB\TDCSK.mdb")
        
          'Attach the Microsoft Excel 5.0 table "MyTable" from the file
          'Book1.xls to the Microsoft Access database.
          Set XLTable = db.CreateTableDef("Temp")
    
          XLTable.Connect = "Excel 5.0;DATABASE=D:\Book1.xls"
          XLTable.SourceTableName = "MyTable"
          db.TableDefs.Append XLTable
    
         'Run the append query that adds all of the records from MyTable
         'to the tblCalcSht table.
          strSQL = "Insert into tblOC_CalcSht Select * from Temp"
            
         'Execute the SQL statement.
          db.Execute strSQL
    
         'Remove the attached table because it's no longer needed.
          db.TableDefs.Delete "Temp"
        
          db.Close
      
    Else    ' User chose No.
        Cancel = True
    
    End If

End Sub

Your help is higly appreciated.

Thank you & Regards,

qwerty70

 

[JerryKlmns]

qwerty70

Did you by any chance join a workgroup between the previous and this run of that code?

 

[qwerty70]

JerryKlmns,

Thanks for your prompt reply. Yes, TDCSK.mdb is joined to a workgroup called MySystem.mdw which also is located in the same folder.

Thanks,

qwerty70

 

[qwerty70]

Has this something to do with ODBC driver? I got this piece of code from the internet but I don't know how to insert this into my existing code.

Conn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=c:\somepath\mydb.mdb;" & _
"SystemDB=c:\somepath\mydb.mdw;", _
"myUsername", "myPassword"

Please help the novice. Thanks.

qwerty70

 

[JerryKlmns]

I think that you need to add these lines before
Set db = OpenDatabase("H:\206_PDDM\TDC_DB\TDCSK.mdb")

Dim dbe As DAO.DBEngine
Dim ws As DAO.Workspace
Dim db As DAO.Database
Set dbe = New DAO.DBEngine
dbe.SystemDB = ""H:\206_PDDM\TDC_DB\MySystem.mdw"
Set ws = dbe.CreateWorkspace("myWS", "[b]UserNameHere[/b]", "[b]ThePasswordHere[/b]")
Set db = ws.OpenDatabase("H:\206_PDDM\TDC_DB\TDCSK.mdb")
....

 

[qwerty70]

JerryKlmns, a million thanks to you. It worked!!! I really appreciate your time and effort in sorting this out. A star for you.

Only a slight typo...i just removed the extra double quote on the dbe.SystemDB = .... line.

Best Regards,

qwerty70

 

[JerryKlmns]

Glad to be of help. I had trouble dealing with secured mdbs using the DAO library myshelf. The bloody SystemDb is not so easily found in help. But I dropped it and moved to ADO & ADOX in order to reuse the code connection to SQL Server aswell.

Thanx for the pinky!