Solved

Insert Record into Access DB using VBScript

Posted on 2014-01-02
5
3,717 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 25

Accepted Solution

by:
chaau earned 500 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

CHALLENGE LAB: Troubleshooting Connectivity Issues

Goal: Fix the connectivity issue in the lab's AWS environment so that you can SSH into the provided EC2 instance.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

630 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