?
Solved

Access 64 bit memo field not working

Posted on 2013-11-23
12
Medium Priority
?
488 Views
Last Modified: 2014-01-16
I have a access 2010 db working on both 32 bit and 64 bit computers.  One of the functions works fine on 32 bit but freezes on 64 bit computers.

The function is to copy records from the current database into another database which is used for searching.  It copies one client record and all its client contact records into one ClientSearch record and concatenates contact names into one field.  

So, in the current database contact name is a TEXT field in the Client table, and in the search database the contact field is a MEMO field in the ClientSearch table.  I loop through all contact records for the current client and set
SearchDBRecordSet!ClientContact = SearchDBRecordSet!ClientContact & CurrentDBRS!ClientContact

As I said, this works fine on 32 bit Office, but not on 64 bit Office.

I have tried all sorts of things and have determined that the following work:
 SearchDBRecordSet!ClientContact = SearchDBRecordSet!ClientContact
SearchDBRecordSet!ClientContact = "testing str"
SearchDBRecordSet!ClientContact = VariantVar (where VariantVar="testing str")
SearchDBRecordSet!ClientContact = CStr(VariantVar)

But these do not work:
SearchDBRecordSet!ClientContact = SearchDBRecordSet!ClientContact & CurrentDBRS!ClientContact
SearchDBRecordSet!ClientContact = CurrentDBRS!ClientContact
SearchDBRecordSet!ClientContact = CStr(CurrentDBRS!ClientContact)

Does anyone hae any idea how I can get the memo field update to work?  The MEMO field I have trouble with is called  !ClientContactDesignation in the code below.

Here is the code:
Public Sub ClientContactInsert(ByVal dbSearch As Database)      'used in update search database
    Dim rs As Recordset
    Dim rsFind As Recordset
    Dim Title As String
    Dim Phone As String
    Dim MobilePhone As String
    Dim Fax As String
    Dim Designation As String
    Dim Email As String
    Dim Name As String
    
    On Error GoTo BadContact
    
    'In search db, set Null to blank string, so that we end up with the resultset
    dbSearch.Execute ("UPDATE ClientSearch SET ClientContactTitle = '' WHERE ClientContactTitle Is Null")
    dbSearch.Execute ("UPDATE ClientSearch SET ClientContactDesignation = '' WHERE ClientContactDesignation Is Null")
    dbSearch.Execute ("UPDATE ClientSearch SET SumName = '' WHERE SumName Is Null")
    dbSearch.Execute ("UPDATE ClientSearch SET Phone = '' WHERE Phone Is Null")
    dbSearch.Execute ("UPDATE ClientSearch SET MobilePhone = '' WHERE MobilePhone Is Null")
    dbSearch.Execute ("UPDATE ClientSearch SET Fax = '' WHERE Fax Is Null")
    dbSearch.Execute ("UPDATE ClientSearch SET Email = '' WHERE Email Is Null")
    

    'Get the all client contact records
    Set rs = DB.OpenRecordset("SELECT * FROM ClientContact ORDER BY ClientID", dbOpenSnapshot)
    
    'Get the recordset from search db
    Set rsFind = dbSearch.OpenRecordset("SELECT * FROM ClientSearch")    

    Do While Not rs.EOF
       
        Title = Replace(rs("Title") & "", "'", "")          'for each field, remove any single apostrophe or vertical bar characters
        Title = Replace(Title, "|", "")
        
        Phone = Replace(rs("Phone") & "", "'", "")
        Phone = Replace(Phone, "|", "")
        
        MobilePhone = Replace(rs("MobilePhone") & "", "'", "")
        MobilePhone = Replace(MobilePhone, "|", "")
        
        Fax = Replace(rs("Fax") & "", "'", "")
        Fax = Replace(Fax, "|", "")
        
        Designation = Replace(rs("Designation") & "", "'", "")
        Designation = Replace(Designation, "|", "")
        
        Email = Replace(rs("Email") & "", "'", "")
        Email = Replace(Email, "|", "")
        
        Name = Replace(rs("Name") & "", "'", "")
        Name = Replace(Name, "|", "")
        
        With rsFind
            .FindFirst ("ClientID = " & rs("ClientID"))
            .Edit
            !ClientContactTitle = !ClientContactTitle & " " & Title
            !ClientContactDesignation = !ClientContactDesignation & " " & Designation
            !SumName = !SumName & " " & Name
            !Phone = !Phone & " " & Phone
            !MobilePhone = !MobilePhone & " " & MobilePhone
            !Fax = !Fax & " " & Fax
            !Email = !Email & " " & Email
            .Update
        End With
        
                           
        rs.MoveNext
        

    Loop
    rs.Close: Set rs = Nothing
    Exit Sub
    
BadContact:
    MsgBox "ERROR in Client Contact Insert : " & err.Number & ", " & err.Description
    rs.Close: Set rs = Nothing

End Sub

Open in new window


The line is freezes on is:
  !ClientContactDesignation = !ClientContactDesignation & " " & Designation

IT also crashes if I change it to:
 !ClientContactDesignation = !ClientContactDesignation & " " & Title

So it is not some strange character in Designation.

Thank you.
0
Comment
Question by:MonkeyPie
[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
  • 6
  • 5
12 Comments
 
LVL 58
ID: 39673229
Odd problem.  Couple of things:

1. Does your app compile?

2. Are you sure your finding a record?  You do a FindFirst, but you don't check for a match.

Jim.
0
 

Author Comment

by:MonkeyPie
ID: 39673251
Hi Jim,

Thanks for response.  Yes, app compiles (on 32 bit and 64 bit computers).  Also, yes it does find a match.  I run it in debug mode with a checkpoint for the first few and all is well.  Also, it would go to the on error trapping bit - and it doesn't.

I have done further testing.  The existing setup has the backend search db as a MDB file, but if I convert it to ACCDB file it all works perfectly.  

For now we are stuck with MDB as some users have not yet moved over to my new front end and are using an old vb6 app that links to the backend MDB.

I I comment out the setting up of MEMO fields it works, but really does not like :
memo field = memo field & string field

although memo field = memo field & "xxx"

works.

My worst case solution is to tell the client all will be well once everyone converts to new front end and we convert all backends to accdb but I would really like a better fix.

I should also add that the program does not give an error message, but I get the ACCESS HAS STOPPED WORKING error and the whole thing dies.

Suzanne.
0
 
LVL 58
ID: 39673268
<<memo field = memo field & string field>>

 Try changing the string name to something like strDesignation

 This is pretty odd....I haven't heard of anything like this.   Odder yet that it works OK with the accdb and not a MDB.

Jim.
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:MonkeyPie
ID: 39673306
I did try this already and it did not work.

very odd indeed - and don't forget that it works perfectly on computers with 32 bit Office or 32 bit Runtime, just not 64 bit situations.

So, there must be something about the difference between memo type and string type that only crops up with 64 bit.  I would like to be able to test converting strDesignation to a memo type and try

memo field = memo field & strToMemo(string field)

 but don't know how.  Any ideas?
0
 
LVL 13

Expert Comment

by:Surone1
ID: 39673480
post a minimal database with bogus data to show the other experts what the problem may be? we don't need your data, or all of your code, just the part where it bugs out.
0
 
LVL 58
ID: 39673923
Sounds like a call to Microsoft is in order.   So few are using 64 bit and it sounds like this is something new.

I'll ask around, but I haven't seen any talk of this till now.

Jim.
0
 
LVL 58
ID: 39673930
<< I would like to be able to test converting strDesignation to a memo type and try
>>

 There is no conversion to a "memo" type; it's not a data type, but simply a storage method within the DB.

 Whether you use text or memo field types, both still work with the string data type.

Jim.
0
 

Author Comment

by:MonkeyPie
ID: 39673944
OK.  Thanks Jim.  I will continue to debug and see if I can define it further.
0
 
LVL 58
ID: 39674575
Not sure how you did your 32 vs 64 bit testing and accdb vs MDB, but I'd like you to try one more thing:

For the DB that's having a problem under 64 bit, create a brand new DB under 64 bit, then import all objects into it from the old DB, then test with that.

I'm wondering if the VBA project file is not corrupt.

Jim.
0
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 1500 total points
ID: 39674623
Also, can you paste here the contents of the variable Designation and Title when they caused the freeze/crash please.

Thanks,
Jim.
0
 

Author Comment

by:MonkeyPie
ID: 39676021
Thanks you for suggestions.  I will give them a try.  I have to wait to get access to the 64 bit computer I test on - will not be today.
0
 

Author Closing Comment

by:MonkeyPie
ID: 39787399
I have abandoned trying to get this to work.   Once we convert all backends to ACCDB format we don't have the problem, and client is happy to wait rather than send time sorting this out,  Thanks for all your help.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

800 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