Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2002
    Posts
    16

    Red face Unanswered: Update MSACCESS 97 Front End Datbase with Visual Basic ...

    Hello everyone!. I have a datbase that it is split in RMABE (back end) and
    RMAEMO (front end) who are residin in the Server, every user has a copy of
    the front end in there local hard drive, So when I make any modifications on
    the front end (RMAEMO) in the server, I need that every users local copy be
    updated. So I wrote this Procedure on a Visual Basic 6.0 form that compares
    the last time the local front end was updated (LocalDbDate) that resides in
    the Last Update field or the Password Table and compares that date with the
    date of the front end that resides in the Server, if the date of the front
    end in the server is later then the front end in the hard drive it deletes
    the copy in the local hard drive a it make a new copy from the front end in
    the server to the local hard drive. Afther the copy is finish then it opens
    the front in the Local hard drive. All that works perfect, but my problem is
    that if I put a SET DATABASE PASSWORD in the back end (RMABE) when the code
    trys to open any table in the database by code (Set dbs =
    OpenDatabase("C:\MARTEK\RMAEMO.MDB") ) it sends a error message:
    Run-time error '70': Permision denied.
    If I take the set database password from the back end the code works
    perfect, but when I put it back it sends me that run time error message. My
    questioned, is there a way to open a access 97 database with a set database
    password by code in Visual Basic 6.0? ... Thanks in advanced.


    Private Sub test()
    Dim dbs As Database, rst As Recordset, strSQL As String
    Dim NetDir As String, LocalDir As String, _
    LocalDbDate As Date, NetDbDate As Date, NetDb As String, LocalDb As
    String, _
    NetDbPicture As String, NetDbDirPicture As String, LocalDbPicture As
    String
    Dim strMACAddr As String, sAppName As String, aAppPath As String

    NetDir = "F:\RMA"
    LocalDir = "C:\Martek"
    NetDb = "F:\RMA\RMAEMO.MDB"
    LocalDb = "C:\Martek\RMAEMO.MDB"
    NetDbPicture = "F:\RMA\Extras\Pictures\RMAEMO.BMP"
    LocalDbPicture = "C:\MARTEK\RMAEMO.BMP"
    NetDbDirPicture = "F:\RMA\Extras\Pictures"
    sAppName = "MS Access"
    sAppPath = "C:\Program Files\Microsoft Office\Office\MSACCESS.EXE
    C:\MARTEK\RMAEMO.MDB /excl"
    strMACAddr = basEthAddr

    If Dir(NetDir, vbDirectory) = "" Then

    Else
    If Dir(LocalDir, vbDirectory) = "" Then
    MkDir LocalDir
    FileCopy NetDbPicture, LocalDbPicture
    FileCopy NetDb, LocalDb
    Shell sAppPath, vbHide
    Exit Sub
    Else
    If Dir(LocalDb, vbDirectory) = "" Then
    FileCopy NetDbPicture, LocalDbPicture
    FileCopy NetDb, LocalDb
    Shell sAppPath, vbHide
    Exit Sub
    Else
    Set dbs = OpenDatabase("C:\MARTEK\RMAEMO.MDB")
    strSQL = "SELECT * FROM [Password Table] " & _
    "WHERE [MacAddr] = '" & strMACAddr & "';"
    Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)

    LocalDbDate = Format(rst![Last Update], "mm/dd/yyyy hh:mm AM/PM")
    rst.Close
    dbs.Close

    NetDbDate = FileDateTime(NetDb)
    NetDbDate = Format(NetDbDate, "mm/dd/yyyy hh:mm AM/PM")

    If LocalDbDate < NetDbDate Then
    Kill LocalDb
    FileCopy NetDb, LocalDb
    Set dbs = OpenDatabase("C:\MARTEK\RMAEMO.MDB")
    strSQL = "SELECT * FROM [Password Table] " & _
    "WHERE [MacAddr] = '" & strMACAddr & "';"
    Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
    rst.Edit
    rst![Last Update] = NetDbDate
    rst.Update
    rst.Close
    dbs.Close
    Exit Sub
    Shell sAppPath, vbHide
    Exit Sub
    Else
    Shell sAppPath, vbHide
    Exit Sub
    End If
    End If
    End If
    End If
    'varDbLastUpdate = Nz(DFirst("[dbLastUpdate]", "dbDatetbl"), varDate) 'Gets
    the Last update Date of the Local Database
    'If Dir(LocalDir, vbDirectory) = "" Then
    End Sub

    Private Sub Form_Load()
    test
    End
    End Sub

  2. #2
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    I store all the db paths of the users in a table and then invoke the following code to move the mde file to their local drives.

    I have several utils that do things like this - here is the easiest one

    Public Sub proc_filetransfer()
    On Error GoTo myerrors
    Dim db As Database
    Dim rst As Recordset
    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblUpdates")
    Dim myfilepath As String
    myfilepath = "\\DELL\DATABASE\Trag MDE v3.mde"
    Dim fs As New FileSystemObject
    Dim FileInQuestion As String
    FileInQuestion = Dir(myfilepath)
    Dim x As Integer
    x = 0
    Dim mycount As Integer
    mycount = DCount("[pilotuser]", "tblupdates")

    If FileInQuestion = "" Then
    MsgBox "Dale, no mde present!"
    Else
    rst.MoveFirst
    Do Until rst.EOF
    rst.Edit
    rst!newupdate = Date
    rst!successful = -1
    rst!transfererror = "None"
    rst!Version = Application.CodeProject.Name & _
    " " & Date

    rst.Update
    x = x + 1
    fs.CopyFile myfilepath, rst!pilotuser, True
    rst.MoveNext
    Loop

    MsgBox "There were " & x & " users that received the new update " _
    & vbCrLf & vbCrLf & "There are " & mycount & " that were to " _
    & "receive the new file." & vbCrLf & vbCrLf _
    & mycount - x & " users still need the update!", vbInformation _
    , "Transfers Complete"

    End If
    myerrors:
    If Err.Number = 52 Then
    rst.Edit
    rst!transfererror = "No connection for " & rst!pilotuser
    rst!successful = 0
    rst.Update
    x = x - 1
    Resume Next
    ElseIf Err.Number = 0 Then



    Else: rst.Edit
    rst!transfererror = Err.Description

    rst!successful = 0
    rst.Update
    x = x - 1
    Resume Next

    End If

    End Sub
    Dale Houston, TX

  3. #3
    Join Date
    Feb 2004
    Posts
    25
    passwords are remembered when you link to the back-end. When you are done testing relink to the final back-end db, this should prompt for the password and save it. Then make the MDE and it will have the passwords remembered...

  4. #4
    Join Date
    Aug 2002
    Posts
    16

    Question

    First of all thanks to both axsprog and glnstanley for answering my email.

    axsprog I check the utility that you send and I think is great, and I think I'm going to implemented in some other applications that I developed. But my main concern is not that the front end and the backend are not linked to each other correctly, yes, I agree that when you linked a front end with a back end that has a Set Database Password it ask only the first time the password and after words it does not. But that is not my problem, my problem is that I want to know why the code I wrote in a Visual Basic 6.0 form and made it a executable, works exactly like I wanted to when the front end is linked to a back end that doesn’t have a Set Database Password, but when linked to back end that does have a Set Database Password it sends me that error message.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •