Solved

Access 64 bit memo field not working

Posted on 2013-11-23
12
469 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need to filter query and have subform updatable 2 24
Access 2016 - query 23 56
deduplicating based on criteria 2 19
Should I keep recordsets open? 3 21
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of utilizing SQL Server functions 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 Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

815 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

13 Experts available now in Live!

Get 1:1 Help Now