5/19/2024 12:34:11 AM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
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!
Forum to discuss general SalesLogix import and data migration topics. View the code of conduct for posting guidelines.
|
|
|
|
Updating SLX data from DBF file
Posted: 17 Nov 06 10:19 AM
|
fiogf49gjkf0d Ryan shared a great article ( http://www.slxdeveloper.com/page.aspx?action=viewarticle&articleid=76) on how to update/insert data that really could be useful for a quarterly update I have to make from a DBF file.
My problem is that my select will have a join to that DBF file and the data to be inserted or updating SLX data will be from the DBF file also. (There is a matching key field in both DBs)
Can I open two separate data connections in the same script? Does ADO allow for that? If so is it as simple as defining both into differently named objects? I'm a bit over my head here but I think this would be more efficient than what is there currently. I'm also looking to move this out of SLX into Visual Studio at some point also. 1. No one but the SLX Admin (me) needs access to this import utility. 2. I'm hoping that VB.NET would be faster than VBScript. |
|
|
|
Re: Updating SLX data from DBF file
Posted: 17 Nov 06 10:26 AM
|
fiogf49gjkf0d Sure, you can have two connections to different sources in the same script.
Dim connslx
Dim conndbf
Set connslx = Application.GetNewConnection
Set conndbf = CreateObject("ADODB.Connection")
conndbf.Open "Connection string to the DBF file"
'now use either one of the connections. For example, read from one and write to the other...
Set rsRead = conndbf.Execute("select * from sometableindbf")
While Not rsRead.EOF
connslx.Execute "update sometable set somefield = '" & rsRead.Fields("somefieldindbf").Value & "' where somekeyfield = '" & rsRead.Fields("somekey").Value & "'"
.MoveNext
Wend
Get the idea? Of course, I would suggest to use an updateable recordset for the writing part, instead of an inline SQL statement.
And yes, this would run drastically faster in .NET than in a script  |
|
|
|
Re: Updating SLX data from DBF file
Posted: 17 Nov 06 10:29 AM
|
fiogf49gjkf0d Yes you can do slx as well vs script to get the data from an external database.
Link your sql server with that dbf file thru some DBF driver(I had some problems with some versions of dbf drivers) and refer to those tables as linkedserver.database.owner.tablename.
If you do it from vb/vb.net , then you can have any number of connections in the same programs and translations and all are part of your program , not connection
hth! |
|
|
|
Re: Updating SLX data from DBF file
Posted: 17 Nov 06 10:39 AM
|
fiogf49gjkf0d Awesome, thanks Ryan, Nandu.
Nandu, I'm sure your idea would work for some, but we have an Oracle server and I'm not familiar enough with that to try what you are suggesting. I'm sure our DBA would be ok with it but we have a nasty review process whenever we want to link to a production database. Something to do with Sarbanes-Oxley or some other auditing, maybe it's just internal.
Ryan, I understand the logic there. I wasn't sure about the syntax but now I should be ok.
Now I just have to wait for VB.NET Express to download. (slow as mud today) |
|
|
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|