Solved

Can't Update Access Database Field

Posted on 2014-01-09
9
349 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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…

733 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