Solved

Access 64 bit memo field not working

Posted on 2013-11-23
12
453 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
  • 6
  • 5
12 Comments
 
LVL 57
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 57
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
 

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 57
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

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

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 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

12 Experts available now in Live!

Get 1:1 Help Now