Solved

Can't Update Access Database Field

Posted on 2014-01-09
9
344 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 32

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
 
LVL 32

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

 

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 32

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 32

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Running Access application from Task Scheduler 6 37
Dropdown Not In List - not working correctly 11 38
Query design issue 2 24
Convert Access formula to SQL 5 20
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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…

863 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now