Updating Linked Tables from a Combo Box

[jloving]

Hi.

I have created a interface from which the user can select a contract from a dropdown list and 'On Change' it runs code to return the value from a query.

Here is an example:

If cboContracts <> 0 Then
myContractSelect = CStr(Left(cboContracts.Value, 8))
updatedContractSelect = myContractSelect
cboContracts = myContractSelect
DoCmd.OpenQuery "qryLocations"
Me.cboContracts = myContractSelect
End If

The value of the combo box is a variation of strings and numbers (e.g. 63000105 Park Avenue). This 8 character value is used to run the query.

I would like to refer to this query which includes the tables that need to be linked and also the database to open.

How can I refer to this query, to loop through each record and link each table to the front end database. Do until the last recordset set is reached. Example code would be much appreciated as I am a beginner.

Thank you in advance and have a great day.

Jackie

 

[grtammi]

I currently don't have Access installed, but something like this should get you started:

function link_db_tables(str_remote_db_name as string, str_query as string)

   dim current_db as dao.database
   dim remote_db as dao.database
   dim rs as dao.recordset
   dim tdf as dao.tabledef

   set current_db = currentdb

   set rs = current_db.openrecordset(str_query, dbsnapshot)

   if not rs.eof then

      ' non-empty recordset 
      while not rs.eof

         ' open remote database connection
         set remote_db = opendatabase(rs("RemoteDatabase"))
         set tdf = remote_db.tabledefs(rs("TableName"))
         tdf.connect=";DATABASE=<path to remote db here>"
         tdf.refreshlink

         ' open table in remote database
         rs.movenext

      wend

      remote_db.close()

   end if

end function

I think something like that will get you started ... sorry it looks a little PHP-like, that's been my lingua-de-franca lately.

HTH

Greg

"for me, the action is the juice.