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

Access/SQL - Update records

P: 1
I have an Access2003 database that we are converting the backend tables to SQL Server Express. Since this is one of my first times dealing with SQL Server Express, I am having a few issues with one of the programs. Basically it takes a temporary file created in the Access database and it needs to update two tables in the SQL database. I have part of the code below - the temporary file is tblRegister. If there is another way I need to do it, please let me know. Also if anyone has a code example of how I should open the tables and loop thru them - update and exit, that would be great. Thanks.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdPost_Click()
  2. Dim db, ldb As Database
  3. Dim rstR, rstC, rstT As Recordset
  4. Dim Msg, Style, Title, Help, Ctxt, Response, MyString
  5. Dim Lst As ListBox
  6. Dim strFill As String
  7. Set Lst = Me![lstMCNumbers]
  8. Style = vbYesNo ' Define buttons.
  9. Title = "Post registry"  ' Define title.
  10. Help = ""   ' Define Help file.
  11. Ctxt = 1000 ' Define topic
  12.         ' context.
  13.         ' Display message.
  14. Response = MsgBox("Post Registry into Customer list?", Style, Title)
  15. If Response = vbYes Then
  16. Set db = CurrentDb()
  17. 'Set db = OpenDatabase("n:\TestData\PascoData_041107.mdb")
  18. Set ldb = CurrentDb()
  19. Set rstR = ldb.OpenRecordset("tblRegister")
  20. 'Set rstC = db.OpenRecordset("tblCustomers", dbOpenSnapshot, dbSeeChanges)
  21. ''Set rstT = db.OpenRecordset("tblTransactions", dbOpenSnapshot, dbSeeChanges)
  22.  
  23. 'rstR.MoveFirst  'take out
  24. Do While Not rstR.EOF
  25.    rstC.FindFirst = "MCNumber=" & rstR.MCNumber '<if MCNumber is numeric
  26.  
  27.  ' rstC.Index = "MCNumber"  'take out
  28.  ' rstC.Seek "=", rstR.MCNumber 'take out
  29.   If rstR![PostIt] = True Then
  30.     If rstC.NoMatch Then
  31.       rstC.AddNew
  32.       rstC![MCNumber] = rstR![MCNumber]
  33.       rstC![DOTNumber] = rstR![MCNumber]  ''03/27/07 added by DRA for DOT#
May 29 '07 #1
Share this Question
Share on Google+
1 Reply


nico5038
Expert 2.5K+
P: 3,072
Hmm, taking the hard way...

I would create unique indexes on the SQL Server tables and use an INSERT query in Access to add the not existing rows.
As the Unique index will prevent duplicates to be inserted, this is a safe (and fast) way to add new rows into a table.

Getting the idea ?

Nic;o)
Jun 2 '07 #2

Post your reply

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