Update Front End Tables with Back End Data

[planetdrouin]

Hello,

I am rather new to Access, so...

I have a split database, with some seldom changed tables remaining in the front end. I would like to be able to add code to my front end app, such that everytime a person logs on, these front end tables are updated with data from back end tables. These front end tables include such things as currency exchange rates and department names.

Any help would be greatly appreciated.

Lawrence

 

[sdimaggio]

move the tables to the back end. then link them to the front end.

then front end should only have applications in it. ie. forms, reports, query, etc.

 

[Frink]

Hallo,

I'd agree with sdimaggio, that it's less hassle to have all your data in the back-end. If you really want to do as you've suggested, here are the steps you could use:

On opening the front-end, create a link to the back-end tables, then use the RunSQL command to delete the contents of the front-end copy, then run another SQL to copy the back-end data to the front-end. If your front-end tables are linked with relationships you may not need to explicitly delete all the tables (if cascade deleting is on) and you may have to do the copying in a specific order.

Please note that whilst this will work, it will probably take ages and your users will curse you everytime they open their database. Well, maybe that's a bit harsh, but you might want to consider how long these operations will take.
You could reduce this by only doing it once a week or something like that.

Here's a function to run an SQL action query:

Function ysnRunSQL(ByVal pstrSQL As String) As Boolean
On Error GoTo Err_ysnRunSQL
  ' Comments  : Runs a SQL string action query
  '           : Note: SetWarnings is set to True by this function
  ' Parameter : strSQL - SQL string to execute
  ' Returns   : True if successful, False otherwise
  '
  DoCmd.SetWarnings False
  If ysnQueryExists(pstrSQL) Then
    DoCmd.OpenQuery pstrSQL, acViewNormal
  Else
    DoCmd.RunSQL pstrSQL, True
  End If
  ysnRunSQL = True
Exit_ysnRunSQL:
  DoCmd.SetWarnings True
  Exit Function
Err_ysnRunSQL:
  ysnRunSQL = False
  Resume Exit_ysnRunSQL
End Function
[\code]

SQL to delete records from a table is:
DELETE * FROM tblFrontEndTable

and to copy from one table to an (identical structure) other is

INSERT INTO tblFrontEndTable SELECT tblBackEndTable.* FROM tblBackEndTable;
Note: this will not preserve any autonumber fields

Alternatively you could delete the whole front-end table and import the back end table every time.

- Frink