Access 2003-How to export a table and then reimport it later

[emajka21]

I am working on an HR database for my work. I need to be able to "update" this database once it goes live but the powers that be don't want me to see the salaries. So my boss wants me to create a macro that allows HR to export the salary table and replaces the salary fields with bogus numbers. Then once I am done doing what ever I have to do he wants another macro that reimports that data.

I can export the data to an external database (using TransferDatabase function) and change the data, my problem is reimporting the data. Every import function I have found that is built in creates a New table and doesn't update the original table. I figure I am going to have to write code to do a sql update but I am not quite sure how to do this.

External database I am exporting to: h:\salary-rate.mdb.
Table name in both databases: Salary-Rate
Fields in Salary-Rate:
EmployeeID
EffectiveDate
Salary
HourlyRate
GradeNum
PromotionNotes


Any help you could give me is MUCH appreciated!

[ChipR]

Not tested, but try something like this:

Set dbSource = DBEngine.OpenDatabase("h:\salary-rate.mdb")
set records = dbSource.OpenRecordset("Salary-Rate)
 
'may want DoCmd.SetWarnings False
 
while not records.eof
    strSQL = "UPDATE Salary-Rate SET Salary = " & records!Salary _
     & " WHERE EmployeeID = " & records!EmployeeID
 
   DoCmd.RunSQL strSQL
 
   records.MoveNext
wend
 
'and DoCmd.SetWarnings True
 
 

[emajka21]

I keep getting a syntax error in my update statement. I have tried it numerous different ways and I am not sure what the correct syntax should be.

This is the last version I tried:
strSQL = "Update Salary-Rate SET Salary = rstSalaryRate!Salary WHERE EmployeeID = rstSalaryRate!EmployeeID"

I am sure I am missing some kind of ', &, or ".

I have also tried Me.EmployeeID = rstSalary![EmployeeID].

Any suggestions as to the correct syntax?

[ChipR]

Your record!fieldname is a variable, and so it has to be evaluated before it's inserted into the string. The & basically means + for strings. Try to make sure it looks like my code. You need the & even if it's all on one line.

[emajka21]

I am getting it to work now "sort of". My statement is below:
strSQL = "Update SalaryRate SET Salary = " & rstSalaryRate!Salary & ", HourlyRate = " & rstSalaryRate!HourlyRate & " WHERE EmployeeID = " & rstSalaryRate!EmployeeID & " and EffectiveDate = " & rstSalaryRate!EffectiveDate & ""

I don't get a syntax error any more but no records are being updated. If I removed the " and EffectiveDate = " & rstSalaryRate!EffectiveDate & "" part rows are updated. But an employee could have several records because they will get a new record everytime they get a raise. This database will keep their history. I have a feeling it has to do with the date format but again what I am trying isn't working.

Any more suggestions would be much appreciated!

Thanks!

[ChipR]

Dates in the SQL string have to be surrounded by #, that's just how SQL does it. So if rstSalaryRate!EffectiveDate is a date use:

EffectiveDate = #" & rstSalaryRate!EffectiveDate & "#"

Strings, on the other hand need to be surrounded by " or '. So if it's a string use:

EffectiveDate = """ & rstSalaryRate!EffectiveDate & """"
Two " in a row in a string makes one " in the resulting string.
or
EffectiveDate = '" & rstSalaryRate!EffectiveDate & "'"
That's single quote, double quote & ... & double quote, single quote, double quote.

[emajka21]

I got it work. Thank you for your help. I understand how it works but the syntax gets me everytime! :)