?
Solved

Insert Record into Access DB using VBScript

Posted on 2014-01-02
5
Medium Priority
?
4,266 Views
Last Modified: 2014-01-03
This should be simple and straightforward, but I've spent all day and had no success. I'm updating one table and, based on the results, attempting to insert a record in another table. The first update (to the members table) is working.  But I have had no success with the insert to ilcaMembers:

'define subroutine to handle "all" payments ##
sub allPayments()  ' begin sub ###########################################################
   
   set conn=Server.CreateObject("ADODB.Connection")   conn.Provider="Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath ("../../DB/ILCAEVENTCALENDAR.MDB") & ";"
   conn.Open
   set rs = Server.CreateObject("ADODB.recordset")

   sql = "UPDATE members SET members.paypalStatus = '" & payment_status & "' Where (((members.memID) = '" & memberID & "'));"
   rs=conn.Execute(sql)
      
   sql = "SELECT * FROM members WHERE (memID = '" & memberID & "');"
   rs=conn.Execute(sql)
   
   districtCode = rs.district   
   If (rs.appType = "M" or rs.appType = "R") And (Trim(payment_status) = "Pending") then

      conn.close  
	
	  set conn=Server.CreateObject("ADODB.Connection")	  conn.Provider="Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath ("../../DB/ILCADATA.MDB") & ";"
	  conn.Open	 
	  set rs = Server.CreateObject("ADODB.recordset")
   
      sql = "SELECT districtName FROM districts WHERE (districtCode = '" & districtCode & "');"
      rs=conn.Execute(sql)
      districtName = rs.DistrictName
	  
	  sql = "INSERT INTO ilcaMembers (memberID, districtName) VALUES ([" & memberID & "], [" & districtName &"])"

      rs=conn.Execute(sql)

      conn.close

   End If
end sub  'end sub 

Open in new window


There are no error messages.
0
Comment
Question by:slegy
  • 3
4 Comments
 
LVL 25

Accepted Solution

by:
chaau earned 2000 total points
ID: 39752809
Modify sql to this (if the memberID column in ilcaMembers table is numeric):
 sql = "INSERT INTO ilcaMembers (memberID, districtName) VALUES (" & memberID & ", '" & districtName &"')"

Open in new window

Modify sql to this (if the memberID column in ilcaMembers table is TEXT):
 sql = "INSERT INTO ilcaMembers (memberID, districtName) VALUES ('" & memberID & "', '" & districtName &"')"

Open in new window

Most likely the former is correct. I just added the latter in case the memberID column in ilcaMembers table is TEXT
0
 

Author Comment

by:slegy
ID: 39754199
Well, I just determined that the insert query is not the problem - at least at the moment. What I'm trying to do is update a specific record (lines 8,9 - which complete successfully), then retrieve that same record and use information from it to append a record in a table in another db. Lines 11 and 12 execute, then nothing happens beyond that. I tried closing and reopening the db, but I got the same results. Is this the wrong way of going about it?

Thank you for the tip about the code.
0
 

Author Comment

by:slegy
ID: 39754353
Found the problem - bad code!
0
 

Author Closing Comment

by:slegy
ID: 39754827
It was my coding error, but being sure about the insert code saved a lot of time. Thank you.
0

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question