Solved

Can't Update Access Database Field

Posted on 2014-01-09
9
346 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
  • 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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 350 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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 different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

809 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