Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Access 64 bit memo field not working

Posted on 2013-11-23
12
Medium Priority
?
495 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

604 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