Macros database link to another company 3

[iwells]

Hi,

Is it possible to create a database link to another company other than the database the user is currently logged in to? If so, how?

Through some of the my other posts I've found the macro uses the current session information for the dblink to the company you're logged in to which is great because the user is logged in to the company I want to grab the information I need, but the information needs to go in to a different database which the user isn't logged into. They could in theory log in to that company however.

Basically I need to do an information pull from Company A to Company B through a macro and I'm looking to do it through the API rather than the existing ADO if possible.

If the user is logged in to company A, how do I connect to company B through a session/database link? Both companies are on the same SQL server.

Thanks

 

[iwells]

Is it just as simple as session.open UserID, Password, CompanyB, Date, Flags?

 

[iwells]

Also I see other posts recommending .Clone of the current session in which i supply Company B as the CompanyID correct?

 

[Jay Converse]

Yes, use .Clone

 

[iwells]

Ok, kind of struggling with this due to lack of experience.

First, the current session the user is logged in to doesn't need to be declared/opened since that's done automatically, but how do I reference it in code? Is it given a default variable name? I'm wondering this so that I can access the properties associated with it.

Second, how to I clone the current session for company A to company B and assign the database/company reference from company A to company B? .Clone is easy enough I believe, but the assignment of the database/company reference is something I'm not understanding.

Thanks.

 

[Jay Converse]

Public SessionTo As AccpacSession
Public a4wLink_To_Read As AccpacDBLink
Public a4wLink_From_Read As AccpacDBLink

Set Session_To = a4wLink_From_Read.Session.Clone("TOCOMP")
Set a4wLink_To_Read = Session_TpenDBLink(DBLINK_COMPANY, DBLINK_FLG_READONLY)

 

[iwells]

Excellent thanks!

 

[iwells]

It's now saying you must supply the correct username, password and database ID to open a session. I'm logged as ADMIN on our test server, but I've tried logging on as a test user too that I've confirmed exists in both database and in SQL. Am I missing something?

 

[Jay Converse]

Both databases have to be on the same server, on the same start list.

 

[iwells]

They're both definitely on the same server, not sure what you mean by same start list.

 

[Jay Converse]

When you login to Accpac, there's a start list to select each company.

 

[iwells]

Ahh ... yea definitely still the same. Resetting the passwords on each database just to see .

 

[Jay Converse]

The database passwords have nothing to do with .Clone, that's handled by the start list. And besides, _all_ databases should be using the same login and password.

 

[DjangMan]

Just to answer a question you had - the default global session variable is called AccpacSession.

 

[iwells]

Ok, have that working now to expand on the situation a bit further (I'm fine with looking and other VBA techniques so I'll show what I was trying to do:

For intCnt = 2 To 5

Select Case intCnt
Case 2
strSQL = "SELECT GLASV.SEGVAL, GLASV.SEGVALDESC FROM GLASV WHERE GLASV.IDSEG = '000002' ORDER BY GLASV.SEGVAL"
Case 3
strSQL = "SELECT GLASV.SEGVAL, GLASV.SEGVALDESC FROM GLASV WHERE GLASV.IDSEG = '000003' ORDER BY GLASV.SEGVAL"
Case 4
strSQL = "SELECT GLASV.SEGVAL, GLASV.SEGVALDESC FROM GLASV WHERE GLASV.IDSEG = '000004' ORDER BY GLASV.SEGVAL"
Case 5
strSQL = "SELECT GLASV.SEGVAL, GLASV.SEGVALDESC FROM GLASV WHERE GLASV.IDSEG = '000005' ORDER BY GLASV.SEGVAL"
End Select

With rsSource
.Source = strSQL

If Not .State = adStateClosed Then
.Requery
Else
.Open
End
End With

With rsDestination

.Source = strSQL

If Not .State = adStateClosed Then
.Requery
Else
.Open
End
End With

With rsSource

If Not (.BOF And .EOF) Then

.MoveFirst

With rsDestination

If Not (.BOF And .EOF) Then

Do
.MoveFirst
.Seek rsSource.Fields("SEGVAL"), adSeekFirstEQ

If Not .EOF Then

'code add missing segment
End If

rsSource.MoveNext
Loop Until rsSource.EOF
End If
End With
End If
End With
Loop
End Sub

Basically we have a country that needs multiple sets of accounting books, so I'm reading our IFRS accounts from one company database, then inserting them in to another company.

I can do this in ADO, but I've read so many other posts saying to use the APIs because of verification that I'm forcing myself to learn this.

My questions now are how do you test .BOF/.EOF? If I create a view, say GL0021 for GLASV, how do I filter that view down for only my specific segment values (.Browse followed by .Fetch or just .Read)

Rather than use views, should I just use CS0120 which seems to be highly recommended?

I really wish there were comprehensive document of the methods and properties that didn't require purchasing the .SDK (though I'm going to push for that at some point).

 

[DjangMan]

Having the SDK won't teach you anything that you can't learn yourself. Save your money.

Record a macro doing that things that you want to do. Sage will write most of the code for you and then you can just fix it up.

.Browse is typically followed up with a .Fetch. .Fetch will return True if another record was found and False if no more records were found.

.Read is used when you know the index key field values (and .Read is completely dependent on .Order) .Fetch will use the current .Order but it will read through the records to locate the next record that will satisfy your .Browse filter.

.Browse is like your where clause

 

[Jay Converse]

Purchase the GL consolidations module, then no programming is required.

 

[iwells]

Thanks DjangMan ... problem is I feel bad asking these questions because I know the fundamentals of the same thing in VBA, but finding a clear explanation of some of these items on here is tough.

 

[iwells]

Tuba2007: that's actually exactly what I want to do, but that still doesn't solve the issue of missing accounts from one company to another.

 

[Jay Converse]

Missing accounts is not an issue, the module takes care of that if you set it up correctly.