The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
Link To MS Access Data
Posted: 06 Nov 07 11:39 AM
|
Sorry abotu this but i am a newbie!!!
I am trying to use the below code to import and then link an MS Access record to a SLX record via the ContactID. Would someone be able to tell me where i am going wrong or if this is a stupid idea? Much appreciated???!!!
Dim objConnAccess
Dim objRSAccess
Set objConnAccess = CreateObject("ADODB.Connection")
objConnAccess.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data\MyAccessData.mdb"
'Set objRSAccess = objConnAccess.Execute("select * from customers where CompanyName = 'tesco'")
Set objRSAccess = objConnAccess.Execute("select * from customers where AccessContactID = 'SLXContactID'")
With objRSAccess
ListBox1Access.Items.Clear
While Not (.EOF Or .BOF)
ListBox1Access.Items.Add .Fields("ContactID").Value & ""
.MoveNext
Wend
.Close
End With
Set objRSAccess = Nothing
objConnAccess.Close
Set objConnAccess = Nothing |
|
|
|
Re: Link To MS Access Data
Posted: 06 Nov 07 4:24 PM
|
There may be a better way - but I need to clarify what you are trying to achieve.
Are you trying to get a list of all the SLX ContactIDs into an Access table? If so, you can do this with a single SQL INSERT INTO statement rather than your suggested coded iterative approach above. Faster and simpler.
What are you going to do with the Access table then?
PP |
|
|
|
Re: Link To MS Access Data
Posted: 07 Nov 07 3:13 AM
|
Phil,
Thanks for the response - i am trying to show additional contact data that is contained in an MS Access database within SLX. I am just testing this at the moment so i have used the ContactID field as the link.
Database Path: C:\Data\MyAccessData.mdb
Table Name: customers
Field1: ContactID
Field2: Type
I hope that clarifies what i am trying to do!! 
Thanks, PA |
|
|
| |
|
Re: Link To MS Access Data
Posted: 07 Nov 07 4:14 PM
|
What version of SLX are you using? You may be able to create a SQL view in the SLX database and then use that as the base for your display in SLX - assuming that there are no remotes, of course.
Otherwise, I guess you may be looking at a scheduled job to import the Access data into SLX so that it syncs out to remotes.
P |
|
|
|
Re: Link To MS Access Data
Posted: 09 Nov 07 4:26 AM
|
Thanks for your repsonses!!! Ryan, is not that something isn't working but i am trying to add a new section into SLX but i havn't got a clue where to start. Any advice on this would be appreciated. Thanks.
Phil, i would be really interested to find out more about the SQL view to get the MS Access data into SLX. Would you be able to advise where to start.
I am running SLX V6.2.4
I appreciate any advice.
Thanks, Paul |
|
|
|
Re: Link To MS Access Data
Posted: 11 Nov 07 4:50 PM
|
In later versions of SalesLogix (I do not know exactly when this feature became available), my understanding is that is it is possible to design a SQL Server view outside of SLX (ie using pure SQL Server tools) and then to 'register' the view within SLX so that it can be referenced and accessed much like any other SLX table. This 'registration' process occurs within DB manager, I think.
I use the phrase 'my understanding' because I have not yet had cause to do this - it's just something that I'm sure I'll need one day...
I do not know whether such a view can be updateable in SLX (as you probably know, some SQL views are updateable and some are not, depending on various factors).
By the way, have you considered 'upsizing' your Access db to use SQL Server as its data storage medium? You can retain your Access GUI, while harnessing the strengths of a proper DBMS for data storage and retrieval. And the link to SLX will be much easier ...
PP
|
|
|
|
Re: Link To MS Access Data
Posted: 12 Nov 07 6:35 AM
|
Thanks for your advice on this but i am beginning to think that this maybe too advanced for me to implement - even with your advanced assistance.
How easy is it to set up a scheduled task to import the data?
P.S. the MS Access data is the way that a financial system of ours stores/manages it's data, changing this is obviously not an option.
Thanks, Paul |
|
|
|
Re: Link To MS Access Data
Posted: 12 Nov 07 4:47 PM
|
Any interface between heterogeneous systems is more complicated than you expect - always. If you are not experienced enough in development to go down the path of setting up a view as I described, I would not recommend that you consider setting up an import routine either - it's more work.
You need to consider separately what happens with INSERTs, UPDATEs and (maybe) DELETEs. OK - an INSERT is pretty easy - if no match between source data and SLX, create a record. But UPDATEs are not so easy - if a record exists in both systems, how do you know whether it needs to be updated in SLX? Use Date Last Updated, a binary hash, a field-by-field comparison?? Another option is a complete DELETE and recreate of all interface data - but maybe this takes too long / causes performance issues / causes monster sync issues ...
And BTW, someone needs to start planning the migration of that financial system if it's at all business-critical  |
|
|
|
Re: Link To MS Access Data
Posted: 13 Nov 07 3:59 AM
|
Hi Paul
You may want to consider the use of a third party tool such as Scribe or Inaport (from us).
These tools provide a user interface that makes it fairly easy to automate the import of data into your system, and look after the process of matching against your SLX data and maintaining the business integrity of the data.
Inaport is sold by Sage as the suggested product for integration purposes for SLX, CRM and CRM.com. You can see a couple of movies on the capabilities of Inaport at:
http://www.inaplex.com/Resources/Tutorials.aspx
Information on Scribe is available from:
http://www.scribesoftware.com/
HTH
Regards
David Evans
Inaport - CRM Integration
http://www.inaplex.com |
|
|
|
Re: Link To MS Access Data
Posted: 13 Nov 07 8:19 AM
|
Phil,
Thanks for your help with this but i think some more reading/understanding is needed for me to be able to get ths done!!
I will look into views as this is something that i never really understood.
I will obviously come back if i have any problems.
Thanks, Paul |
|
|
|