|
stephenbaer
Yak Posting Veteran
71 Posts |
 Posted - 2011-08-22 : 18:56:36
|
| I'm trying to create an updater for a database I created for a client. Obviously, this would be easier if I just split the database, thus making this unecessary, but the client wants the whole thing (data & objects/code)for each of his clients in one mdb. He just makes copies of the master mdb and changes the necessary info, but then if we find a bug, we have to update each mdb. It would be nice if I could just drop all the tables, and import the new ones, but relationship restrictions prevent it on one of the tables. Here's the code: (the parameters are supplied by a file dialog selection) ----------------------------------------------------------- Public Function fnUpdate(varMaster As Variant, varTarget As Variant) As Boolean Dim stTargetFile, stTargetPath, stNewFilename As String 'Isolate the path stTargetPath = Left$(varTarget, InStrRev(varTarget, "\")) 'Isolate the filename stTargetFile = Mid$(varTarget, InStrRev(varTarget, "\") + 1) 'Strip the extension from the filename stTargetNoExt = Left$(stTargetFile, InStrRev(stTargetFile, ".") - 1) 'Get the version number (stVersion) from the version table in the Master Dim db As DAO.Database Dim rs As DAO.Recordset Dim strSQL As String strSQL = "SELECT Max([versionID]) AS [maxVersion] " _ & "FROM [tblVersion] IN '" & varMaster & "';" Set db = CurrentDb 'Debug.Print strSQL Set rs = db.OpenRecordset(strSQL) stVersion = rs!maxVersion rs.Close Set rs = Nothing Set db = Nothing 'Create a new filename for the updated version stNewFilename = stTargetPath & stTargetNoExt & "_" & stVersion & ".mdb" 'copy the new master, Using the target file name and the new version number FileCopy varMaster, stNewFilename 'update the only record in the employer table in the new file from the employer table in the old (target) file Set db = OpenDatabase(stNewFilename) strSQL = "UPDATE Employer SET EmpName, ProgName, ProgNum, Address, Contact, Capacity, Telephone, " & _ "Training, PaidRCL, ActualOcc, FYStart, FYEnd " & vbCrLf & _ "(SELECT EmpName, ProgName, ProgNum, Address, Contact, Capacity, Telephone, " & _ "Training, PaidRCL, ActualOcc, FYStart, FYEnd " & vbCrLf & _ "FROM Employer IN '" & varTarget & "' WHERE EmployerID = 1) " & vbCrLf & _ "WHERE EmployerID = 1; " Debug.Print strSQL db.Execute strSQL Set db = Nothing fnUpdate = True End Function -------------------------------------------------------------------------- There is something not right in the UPDATE statement, but I can't figure out what. Inner Join on EmployerID, maybe? Help! Thanks in advance!-----------------Stephen |
|
update...select from external database