Log in Register FAQ Memberlist Search ContactReview Forum Index
A GoldMine Discussion and Support Community

ContactReview Forum Index » GoldMine 8 - 2019 (Premium Edition) » Pushing notes to GM from Access
Post new topic  Reply to topic View previous topic :: View next topic 
Pushing notes to GM from Access
PostPosted: Sun Jul 12, 2015 1:27 pm Points: 0 Reply with quote
kgitts69
n00b
Joined: 12 Jul 2015
Posts: 6




Hi everyone,
Our company just upgraded from CE 7.6 to the newest PE of Goldmine.
We have a home grown access front end that handles many functions that Goldmine cant (lodging, scheduling, order entry etc...).

One of the main issues I am having is that the new CONTHIST and CAL NOTES fields are now data type IMAGE. I have been able to create a view in SQL SSMS that converts the image datatype to binary and then varchar and am using that view as a linked table in access for any module that needs to display notes but my issue is with modules that need to insert or append notes to a client record from access. The normal update notes query doesn't work because its updating with a text data type and I do not know how to convert (or if its even possible) in an access VB SQL query to data type image so I am at a loss.

I am sure we are not the only company that has used an access front end with GM and upgraded so I was wondering if anyone has come up with a way to update or insert notes into a client record that GM will then recognize. Right now it seems to only recognize the first letter of whatever the note is in GM. I do not think Access has a binary data type, its just OLE when I look at the table design for CAL in access so I don't even know what I should be trying to convert the notes to.

Any suggestions?

Thanks in advance

Kevin
View user's profile Send private message

PostPosted: Mon Jul 13, 2015 11:21 am Points: 0 Reply with quote
DJ
GoldMine Guru
Joined: 29 Jun 2006
Posts: 2055
Location: Fitchburg, MA, USA




As you surmised, the ContHist.Notes & the Cal.Notes are in fact Image fields.

Issue:

Schema changes

Resolution:

1) We added the Notes table

2) We changed column type from "text" to "image" for columns ( CAL->Notes, CONTHIST->Notes, INFOMINE->Notes, MAILBOX->RFC822 ).

3) To read data in the previous format you can use, for example, the next query:

select cast( cast ( NOTES as varbinary(max) ) as varchar(max) ) from dbo.CAL

You can either do this through DDE or with the hard coding functions. Both of these are accomplished using VBO scripting, I believe. If this is beyond your capabilities then you may wish to hire an Access Coding expert. OLE DB will work if you know how to code the On Mouse Exit functionality of the Access Field or On Form Close of the Access Form.

_________________
DJ Hunt

Phone: (978)342-3333
Email: DJ@DJHunt.US

GoldMine Premium - The Defintive Guide
One-on-One GoldMine Technical Support ( Fee Based )
www.DJHunt.US
View user's profile Send private message Send e-mail Visit poster's website AIM Address Yahoo Messenger MSN Messenger ICQ Number

writing to notes field
PostPosted: Tue Jul 14, 2015 3:07 am Points: 0 Reply with quote
kgitts69
n00b
Joined: 12 Jul 2015
Posts: 6




Right, I totally understand read the data type what I'm asking is what is the best method to write notes back into the cal or conthist fields from access. Anyone have any ideas?

Thanks again in advance
Kevin
View user's profile Send private message

PostPosted: Tue Jul 14, 2015 6:22 am Points: 0 Reply with quote
DJ
GoldMine Guru
Joined: 29 Jun 2006
Posts: 2055
Location: Fitchburg, MA, USA




I do believe that I answered that question in the last paragraph:

"You can either do this through DDE or with the hard coding functions. Both of these are accomplished using VBO scripting, I believe. If this is beyond your capabilities then you may wish to hire an Access Coding expert. OLE DB will work if you know how to code the On Mouse Exit functionality of the Access Field or On Form Close of the Access Form."

_________________
DJ Hunt

Phone: (978)342-3333
Email: DJ@DJHunt.US

GoldMine Premium - The Defintive Guide
One-on-One GoldMine Technical Support ( Fee Based )
www.DJHunt.US
View user's profile Send private message Send e-mail Visit poster's website AIM Address Yahoo Messenger MSN Messenger ICQ Number

Thanks
PostPosted: Tue Jul 14, 2015 6:28 am Points: 0 Reply with quote
kgitts69
n00b
Joined: 12 Jul 2015
Posts: 6




Well I have tried hard coding through VBO scripting and either I am not getting it or that's not the answer. Still not working. Thanks for trying though. I will probably just hire someone to work on this for a few days rather than spend any more time on it. If you know of anyone I am open to suggestions.

Thanks,

Kevin
View user's profile Send private message

PostPosted: Tue Jul 14, 2015 9:38 am Points: 0 Reply with quote
DougCastell
GoldMine Guru
Joined: 15 Jun 2006
Posts: 1639
Location: Los Angeles, CA




as mentioned on the other forum, you can use my tool to do this: http://www.castellcomputers.com/?page_id=123

or, yeah, you could hire someone (like me, or maybe the person who you purchased GoldMine from) Smile

_________________
Doug Castell

GoldMine Sales and Support:
http://www.castellcomputers.com/
office: (310)601-4738
View user's profile Send private message Visit poster's website AIM Address Yahoo Messenger MSN Messenger ICQ Number

PostPosted: Tue Jul 14, 2015 9:45 am Points: 0 Reply with quote
kgitts69
n00b
Joined: 12 Jul 2015
Posts: 6




Yea, I guess I just don't know enough about Access to understand how that would help me convert data from Access back into GM as data type image in the notes field.

Would you be available for a consult so I could get a quote to give to my accounting dept? I would love to have your help if you can fix this.

Thanks!
Kevin
View user's profile Send private message

PostPosted: Tue Jul 14, 2015 10:11 am Points: 0 Reply with quote
DougCastell
GoldMine Guru
Joined: 15 Jun 2006
Posts: 1639
Location: Los Angeles, CA




You would probably be best served by taking stock of the whole access app idea and considering your options with regards to updating that, rebuilding it, not storing any of it's data in ACCESS and, instead, using SQL tables to store all the data and interact with it all via a VB.net front-end.

That being said, I'm sure I could update it to insert the note into the notes table, as is necessary for this version of GoldMine (the contact1.notes field is deprecated at this point.)

As for an estimate, I guess I'd need to look more closely at the access app to get anywhere near accurate. Give me a call at my office when you have a chance and we can figure something out.

_________________
Doug Castell

GoldMine Sales and Support:
http://www.castellcomputers.com/
office: (310)601-4738
View user's profile Send private message Visit poster's website AIM Address Yahoo Messenger MSN Messenger ICQ Number

PostPosted: Tue Jul 14, 2015 11:21 am Points: 0 Reply with quote
kgitts69
n00b
Joined: 12 Jul 2015
Posts: 6




Thanks Doug, we actually do not store any data in Access itself, we just use it as our front end to handle any functions that we need that Goldmine can't handle. I would love to move the whole thing to .net or coldfusion or anything that is more supportable and more easily accessed but I work for a not for profit company and right now he best we can do is keep this thing running as long as we can.

I will try to give you a call tomorrow mid morning if that is works for you and hopefully this is something that at least temporarily you can help us get working and then if you are interested the longer term project would be rebuilding this from the ground up in something that makes more sense than Access but we can discuss that a well when we talk.

Hope to talk to you soon an thanks again for your assistance
Kevin
View user's profile Send private message

PostPosted: Tue Jul 14, 2015 11:27 am Points: 0 Reply with quote
DougCastell
GoldMine Guru
Joined: 15 Jun 2006
Posts: 1639
Location: Los Angeles, CA




Yeah, I've worked with you guys before (2007) but I don't recall seeing/doing anything in access.

If there's nothing actually stored in access, then yeah, we ought to be able to move on to something like the dashboards in GoldMine or, potentially, pull the existing access forms up to VB.net and extend them from there.

_________________
Doug Castell

GoldMine Sales and Support:
http://www.castellcomputers.com/
office: (310)601-4738
View user's profile Send private message Visit poster's website AIM Address Yahoo Messenger MSN Messenger ICQ Number

Access
PostPosted: Tue Jul 14, 2015 12:04 pm Points: 0 Reply with quote
kgitts69
n00b
Joined: 12 Jul 2015
Posts: 6




Well we do have linked tables to the GM DB as well as our web DB and Quotewerks DB as well as another home grown DB so I don't think we can move everything in to GM. Moving to VB.NET would be terrific but right now my main goal is to get these notes to push properly so we can get started with user testing. The overhaul of the Access front end is a different project and one Ill happily discuss with you but I don't think my time frame or budget will allow for that at the moment.

We can discuss more tomorrow if you are free

Talk to you soon

Kevin
View user's profile Send private message

PostPosted: Thu Jul 16, 2015 7:44 am Points: 0 Reply with quote
DJ
GoldMine Guru
Joined: 29 Jun 2006
Posts: 2055
Location: Fitchburg, MA, USA




If it matters at all, I can highly recommend Doug Castell for this project.

_________________
DJ Hunt

Phone: (978)342-3333
Email: DJ@DJHunt.US

GoldMine Premium - The Defintive Guide
One-on-One GoldMine Technical Support ( Fee Based )
www.DJHunt.US
View user's profile Send private message Send e-mail Visit poster's website AIM Address Yahoo Messenger MSN Messenger ICQ Number

Pushing notes to GM from Access
  ContactReview Forum Index » GoldMine 8 - 2019 (Premium Edition)
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
All times are GMT - 7 Hours  
Page 1 of 1  

  
  
 Post new topic  Reply to topic  


Brought to you by Castell Computers, Doug Castell, Admin
RSS Feed
Powered by phpBB © 2001-2004 phpBB Group
Theme created by Vjacheslav Trushkin