By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,701 Members | 1,928 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,701 IT Pros & Developers. It's quick & easy.

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

P: 38
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:

Any help you could give me is MUCH appreciated!
Dec 9 '08 #1
Share this Question
Share on Google+
6 Replies

Expert 100+
P: 1,287
Not tested, but try something like this:

Expand|Select|Wrap|Line Numbers
  1. Set dbSource = DBEngine.OpenDatabase("h:\salary-rate.mdb")
  2. set records = dbSource.OpenRecordset("Salary-Rate)
  4. 'may want DoCmd.SetWarnings False
  6. while not records.eof
  7.     strSQL = "UPDATE Salary-Rate SET Salary = " & records!Salary _
  8.      & " WHERE EmployeeID = " & records!EmployeeID
  10.    DoCmd.RunSQL strSQL
  12.    records.MoveNext
  13. wend
  15. 'and DoCmd.SetWarnings True
Dec 10 '08 #2

P: 38
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?
Dec 10 '08 #3

Expert 100+
P: 1,287
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.
Dec 10 '08 #4

P: 38
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!

Dec 10 '08 #5

Expert 100+
P: 1,287
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.
EffectiveDate = '" & rstSalaryRate!EffectiveDate & "'"
That's single quote, double quote & ... & double quote, single quote, double quote.
Dec 10 '08 #6

P: 38
I got it work. Thank you for your help. I understand how it works but the syntax gets me everytime! :)
Dec 11 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.