Solved

Insert Record into Access DB using VBScript

Posted on 2014-01-02
5
3,583 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
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 …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

738 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