?
Solved

Can't Update Access Database Field

Posted on 2014-01-09
9
Medium Priority
?
351 Views
Last Modified: 2014-01-10
I've tried everything I can think of but can't find the solution. I have a VBScript page that receives information back from PayPal. I update  a field in a database with the returned payment status code, then retrieve that record and insert a new record in another database. It is very straightforward. All fields update except the last two - last name and first name. When I finally narrowed down the problem yesterday, I manually added a new last name and a new first name field to the table being inserted. Voila! The insert worked. This morning I updated the local query that creates the table to include the two new fields. I uploaded to the web, ran the test and the query failed. I recreated the local query again, removing the sort from the last name field - same results. I can only conclude that the way the table is initially created is the problem, but I can't see it.

Local query creating the table:
SELECT [tblILCA Members].[Member ID] AS memberID, IIf([District Name]="Connecticut/Rhode Island","Connecticut-Rhode Island",IIf([District Name]="US@Large","US Large",[District Name])) AS districtName, [tblILCA Members].[Fleet#] AS fleet, [First] & " " & IIf(Not IsNull([MI]),[MI] & " ") & [Last] AS name, [tblILCA Members].Suffix, [tblILCA Members].Address1, [tblILCA Members].State, [tblILCA Members].Country, IIf(IsNull([tblILCA Members]![City]) And IsNull([tblILCA Members]![State]) And IsNull([tblILCA Members]![Zip]),[tblILCA Members]![Country],IIf(IsNull([tblILCA Members]![City]) And IsNull([tblILCA Members]![State]),[tblILCA Members]![Zip] & " " & [tblILCA Members]![Country],IIf(IsNull([tblILCA Members]![City]) And IsNull([tblILCA Members]![Zip]),[tblILCA Members]![State] & " " & [tblILCA Members]![Country],IIf(IsNull([tblILCA Members]![State]),[tblILCA Members]![City] & " " & [tblILCA Members]![Country] & " " & [tblILCA Members]![Zip],IIf([tblILCA Members]![Country]="US",[tblILCA Members]![City] & ", " & [tblILCA Members]![State] & " " & [tblILCA Members]![Zip],[tblILCA Members]![City] & ", " & [tblILCA Members]![State] & " " & [tblILCA Members]![Zip]))))) AS location, [tblILCA Member Types].[Member Type] AS memType, "" AS boat, "" AS boatName, [tblILCA Members].Last AS [last], [tblILCA Members].First AS [first], [tblILCA Members].[Home Email] AS [password] INTO ilcaMembers
FROM (([tblILCA Member History] INNER JOIN [tblILCA Members] ON [tblILCA Member History].[Member ID] = [tblILCA Members].[Member ID]) INNER JOIN [tblILCA Member Types] ON [tblILCA Member History].MType = [tblILCA Member Types].[Member Code]) INNER JOIN [tblILCA Districts] ON [tblILCA Members].[District Code] = [tblILCA Districts].[District Code]
WHERE ((([tblILCA Member Types].[Member Type])="Skipper" Or ([tblILCA Member Types].[Member Type])="Associate" Or ([tblILCA Member Types].[Member Type])="Crew") AND (([tblILCA Member History].Year)=Year(Date()))) OR ((([tblILCA Member Types].[Member Type])="Life") AND (([tblILCA Member History].Year)="9999"))
ORDER BY IIf([District Name]="Connecticut/Rhode Island","Connecticut-Rhode Island",IIf([District Name]="US@Large","US Large",[District Name])), [tblILCA Members].[Fleet#], [tblILCA Members].Last;

Open in new window


Web query inserting records into this table:
sql = "INSERT INTO ilcaMembers2 (memberID, districtName, fleet, name, suffix, address1, state, country, location, memType, boat, boatName, last, first) VALUES ('" & memberID & "', '" & districtName & "', '" & fleetNo & "', '" & memName & "', '" & suffix & "', '" & address & "', '" & stateProv & "', '" & country & "', '" & loc & "', '" & memType & "', '" & boatNo & "', '" & boatName & "', '" & memLast & "', '" & memFirst &"')"

Open in new window


If I remove the last and first name fields, the query runs correctly. What am I missing?
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
  • 5
  • 4
9 Comments
 
LVL 33

Expert Comment

by:Big Monty
ID: 39768601
do you get any errors returned?

if you hard code values into the sql query for first and last names, will they get inserted?

also, I only see code for ilcaMembers, and in your sql statement, you reference ilcaMembers2
0
 

Author Comment

by:slegy
ID: 39768653
There are no error messages. Apparently, an error is detected and the code just doesn't execute.  The creating query produces the table ilcaMembers, but because it is a live table, I copied the structure to ilcaMembers2. I have to test online because that is the only way I can get the PayPal IPN. I have logged the values of memLast and memFirst and they are correct. I will try hard-coded values.
0
 

Author Comment

by:slegy
ID: 39768980
I'm at my wits end. I created a brand new query from scratch to create the ilcaMembers table. I should note that up to yesterday I was directly updating that table, so the results for both the original table and the copied table structure are the same. In the new query I rearranged the order of the fields. Then I executed a query to update only the Last field with 'Faus'. It didn't execute. Two textfile.WriteLine statements immediately preceding did They reflect the correct first and last names. Both the Last and First fields are 24 characters long, so it isn't a size issue.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 33

Expert Comment

by:Big Monty
ID: 39769012
I've run into issues with Access where field order could be problematic, not sure if that is the case here. Why are you not just updating the table directly?
0
 

Author Comment

by:slegy
ID: 39769235
Because the ilcaMembers table is a live table listing current paid members. I don't want to corrupt it with test data. I thot order might be an issue too. That is why I changed it in the new query. Yesterday I tried doing the update with two queries. That didn't work. Any suggestions as to what else to try?
0
 
LVL 33

Accepted Solution

by:
Big Monty earned 1050 total points
ID: 39769283
I would run a test to see if you can at least update the main table, use a first name of 'aaaaa' so you can easily find it and remove it. By testing this, you'll be able to determine if its the query or the structure of the 2nd table.
0
 

Author Comment

by:slegy
ID: 39769761
I lost two days on something that should have taken a half hour at most. I started back this afternoon adding one field at a time until it again failed. You won't believe this. I decided to give up on the sql query and added the Dreamweaver code to insert a record. Worked the first time!  Don't know what to do about closing this.
0
 
LVL 33

Expert Comment

by:Big Monty
ID: 39771157
very weird, glad you got it working anyways :)

Accept your answer for 0 points and that'll begin the process of closing out the question
0
 

Author Closing Comment

by:slegy
ID: 39771208
Wouldn't let me reduce the points. Thank you for your support and help.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Suggested Courses

777 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