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
NetDbPicture As String, NetDbDirPicture As String, LocalDbPicture As
Dim strMACAddr As String, sAppName As String, aAppPath As String
If LocalDbDate < NetDbDate Then
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![Last Update] = NetDbDate
Shell sAppPath, vbHide
Shell sAppPath, vbHide
'varDbLastUpdate = Nz(DFirst("[dbLastUpdate]", "dbDatetbl"), varDate) 'Gets
the Last update Date of the Local Database
'If Dir(LocalDir, vbDirectory) = "" Then
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!"
Do Until rst.EOF
rst!newupdate = Date
rst!successful = -1
rst!transfererror = "None"
rst!Version = Application.CodeProject.Name & _
" " & Date
x = x + 1
fs.CopyFile myfilepath, rst!pilotuser, True
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"
If Err.Number = 52 Then
rst!transfererror = "No connection for " & rst!pilotuser
rst!successful = 0
x = x - 1
ElseIf Err.Number = 0 Then
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...
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.