How to refer to my backend table in VBA using the set method? 1

[knifey]

Hi,
This should be a very easy question to answer. But I can't find any info on the net.
How do I refer to my backend table in VBA code (DAO) using the set method?
I use 'Set db = Currentdb' all the time in my code. But now I need to run a query on my backend. I've tried various forms of:
<code>Set db2 = "R:\myPathToBackend\mydb_be.mdb"</code>
I've also tried putting the path/filename into a variable, but still no luck.
In Excel VBA I use something similar, something like:
<code>Set wb2 = Workbooks.Open("R:\myPathToSpreadsheet\file.xls", False)</code>
What would be the equivilant in Access VBA?
Thanks,
K

 

[PHV]

Use the OpenDatabase method.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

http://support.microsoft.com/kb/209953

here is an example

 

[TheAceMan1]

How are ya knifey . . .

If the backend is linked (like it should be), just run the query from the front end (as if the tables weren't split).

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

AceMan has a good point. Why would you even have a query in the backend? Backend should have only tables and maybe some unique code for maintenance (back up, compact). All forms, reports, queries, code is in each users front end and can be talored for the type of user. If this is some admin only query then that query should be in the admin users front end, if not then like AceMan said it should be in everyone's front end.

 

[TheAceMan1]

knifey . . .

Sooooo ... is the backend [blue]linked to the frontend?[/blue]

To be sure, this is a [blue]key[/blue] question.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MazeWorX]

for reference ... although the preferred method is as Ace has described above

To open a new db

Dim db As DAO.Database
Set db = DBEngine.Database("C:\MyDatabase.mdb")

To open a db as a user

Dim objWks As DAO.Workspace
Dim db As DAO.Database

DBEngine.SystemDB = "C:\MyDatabase.mdw"
Set objWks = DBEngine.CreateWorkspace("", "user_name", "password")
Set db = objWks.OpenDatabase("C:\MyDatabase.mdb")

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[MazeWorX]

opps sorry first method is

Set db = DBEngine.OpenDatabase("C:\MyDatabase.mdb")

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[TheAceMan1]

knifey . . .

I understand your new to VBA in Access (researched your prior threads). I just want to say ... [blue]don't be afraid[/blue] ... we are all here for you. If there's anything you don't understand ... [blue]just ask![/blue] We'll do the best we can ... OK?

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[knifey]

Thanks for all the pointers. I hope you all had a good new year.
To answer Aceman's question, yes my database is split (access front & backend).
What I'm trying to do is setup automatic front end deployment. I have created a table in the front end and back end with the current version number. When a user opens the front end the VBA checks the 2 version numbers (e.g. if backendVersion > FrontendVersion Then download new fronend to users C drive).
Thanks again

 

[MazeWorX]

you know if the tables are linked you dont need to connect to it to run queries you already are conected only if you are using recordsets then reference it by db = Currentdb(). Also you do realize that if your users have the frontend open you cannot copy a new one over it you will receive a write error.

A work around woold be a third db that starts your app, it opens checks the version numbers downloads and opens the app

Thoughts?


HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[knifey]

Hi Mazeworx,
I'm using some code 'stolen' from the net that works great. It copies the frontend using apiCopyFile. I haven't had a problem with write errors using this.
K

 

[MazeWorX]

hmm interesting. Scratches head; pondering how did I miss this one. I need to read more thanks K

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[knifey]

Hi Mazeworx,
You need not scratch any longer. Here is the code I'm using. I hope you can make as good a use of it as me. Please do let me know if anyone can spot a massive flaw in my plan?
Cheers,
K

Batch file:

REM Check for local frontend file - create directory and copy file if not present and open. else just open.
if not exist "C:\SCdbFrontEnd\Special care database.mde" goto else
if exist "C:\Program Files\Microsoft Office XP\Office10\MSACCESS.EXE" start /max "MSAccess" "C:\Program Files\Microsoft Office XP\Office10\MSACCESS.EXE" "C:\SCdbFrontEnd\Special care database.mde" /wrkgrp "C:\Documents and Settings\%USERNAME%\Application Data\Microsoft\Access\System.mdw" 
if exist "C:\Program Files\MS Office\MSACCESS.EXE" start /max "MSAccess" "C:\Program Files\MS Office\MSACCESS.EXE" "C:\SCdbFrontEnd\Special care database.mde" /wrkgrp "C:\Documents and Settings\%USERNAME%\Application Data\Microsoft\Access\System.mdw"
if exist "C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" start /max "MSAccess" "C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" "C:\SCdbFrontEnd\Special care database.mde" /wrkgrp "C:\Documents and Settings\%USERNAME%\Application Data\Microsoft\Access\System.mdw"
goto endif
:else
mkdir C:\SCdbFrontEnd 
REMxcopy "\\Server\Shared\MyDatabase.mde" "C:\SCdbFrontEnd\MyDatabase.mde" /y 
xcopy "R:\CHS\DentalSpecialCare\SCdb Split\Special care database.mde" "C:\SCdbFrontEnd\" /y 
if exist "C:\Program Files\Microsoft Office XP\Office10\MSACCESS.EXE" start /max "MSAccess" "C:\Program Files\Microsoft Office XP\Office10\MSACCESS.EXE" "C:\SCdbFrontEnd\Special care database.mde" /wrkgrp "C:\Documents and Settings\%USERNAME%\Application Data\Microsoft\Access\System.mdw" 
if exist "C:\Program Files\MS Office\MSACCESS.EXE" start /max "MSAccess" "C:\Program Files\MS Office\MSACCESS.EXE" "C:\SCdbFrontEnd\Special care database.mde" /wrkgrp "C:\Documents and Settings\%USERNAME%\Application Data\Microsoft\Access\System.mdw"
if exist "C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" start /max "MSAccess" "C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" "C:\SCdbFrontEnd\Special care database.mde" /wrkgrp "C:\Documents and Settings\%USERNAME%\Application Data\Microsoft\Access\System.mdw"
endif:

In Access db frontend form:

Private Sub Form_Open(Cancel As Integer)
'check current version of the frontend db.
'if it's out of date, then install new frontend on users C drive (call UpdateFEVersion)
Dim db1 As DAO.Database
Dim rs1 As DAO.Recordset
Dim strQuery1 As String
Dim backEndVersion As String
Dim db2 As DAO.Database
Dim rs2 As DAO.Recordset
Dim strQuery2 As String
Dim frontEndVersion As String
Dim result As String
If CurrentDb.Name = "C:\SCdbFrontEnd\Special care database.mde" Then
    'frontend
    strQuery2 = "SELECT Max(tbCurrentFEbversion.FrontEndVersion) AS MaxOfFrontEndVersion FROM tbCurrentFEbversion;"
    Set db2 = CurrentDb
    Set rs2 = db2.OpenRecordset(strQuery2)
    rs2.MoveFirst
    frontEndVersion = rs2![MaxOfFrontEndVersion]
    'backend
    strQuery1 = "SELECT Max([Development History].New_Version) AS MaxOfNew_Version FROM [Development History];"
    Set db1 = CurrentDb
    Set rs1 = db1.OpenRecordset(strQuery1)
    rs1.MoveFirst
    backEndVersion = rs1![MaxOfNew_Version]
    If backEndVersion > frontEndVersion Then
        result = UpdateFEVersion()
        GoTo endBit
    End If
End If
endBit:
Set db1 = Nothing
Set rs1 = Nothing
Set db2 = Nothing
Set rs2 = Nothing
End Sub

Public Function UpdateFEVersion()
  On Error GoTo ProcError
  Dim strSourceFile As String
  Dim strDestFile As String
  Dim strAccessExePath As String
  Dim lngResult As Long
  'Create the source's path and file name.
'  strSourceFile = "\\server\share\YourFEDatabase.mde"
  strSourceFile = "R:\CHS\DentalSpecialCare\SCdb Split\Special care database.mde"
  strDestFile = CurrentProject.FullName    
  'Determine path of current Access executable.
  strAccessExePath = SysCmd(acSysCmdAccessDir) & "MSAccess.exe "
  If Dir(strSourceFile) = "" Then 'Something is wrong and
                                             ' the file is not there.
     MsgBox "The file:" & vbCrLf & Chr(34) & strSourceFile & _
       Chr(34) & vbCrLf & vbCrLf & _
       "is not a valid file name. Please see your Administrator.", _
       vbCritical, "Error Updating To New Version..."
       GoTo ExitProc
  Else
    'copy the new version of app over the existing one.
    lngResult = apiCopyFile(strSourceFile, strDestFile, False)
  End If                  
  'Modify strDestFile slightly so that it can be used
  ' with the Shell function.
  strDestFile = """" & strDestFile & """"
  MsgBox "Application Updated. Please wait while the application" & _
     " restarts.", vbInformation, "Update Successful"
  'Load new version, then close old one.
  Shell strAccessExePath & strDestFile & "", vbMaximizedFocus
  DoCmd.Quit
ExitProc:
Exit Function
ProcError:
  MsgBox "Error " & Err.Number & ": " & Err.Description, , _
     "Error in UpdateFEVersion event procedure..."
  Resume ExitProc
End Function

So all you need is to make sure the backend and frontend version numbers match in the 2 tables before deployment (to avoid users encountering an infinite download loop).