Accessing Accpac SQL database 2

[Bluejay07]

Hello,

I am creating a program using VB6 and COMAPI. The program will be run from within the accpac environment.

When accpac is running, it has a ODBC connection to the SQL database. What would be the best way to query the database from within my program.

For example, how could I accomplish: Select * from ARCUS WHERE IDCUST = 'XXX'. In addition how would I access each of the fields?

Thanks.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[Bluejay07]

I believe I answered my own question.
Using the browse and fetch method should work.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[Jay Converse]

If you want use pure SQL calls, do this:

Dim CSQuery as AccpacView
Dim sSQL as String
sSQL = "Select * from ARCUS WHERE IDCUST = 'XXX'"

a4wLinkRead.OpenView "CS0120", CSQuery

CSQuery.Browse sSQL, True
CSQuery.InternalSet (256)

Do While CSQuery.Fetch
...
Loop


You can even use the CS0120 view for UPDATE statements, but use at your own risk.

 

[Bluejay07]

Thank you Tuba.
I have seen CS0120 mentioned before, however I keep forgeting about it since I have never used it before.

An update will be required. Since you are cautioning about using CS0120 for updates, do you have any other suggestions?

If at first you don't succeed, then sky diving wasn't meant for you!

 

[ettienne]

Use the views for updates, that way data integrity is maintained.

 

[Bluejay07]

Thanks Ettienne. After posting my last comment, that is what I was thinking but wasn't entirely sure.

Thank you.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[Jay Converse]

All I'm saying is if you bypass the business logic with a pure SQL update, you could have data integrity issues, because there's no validation or error checking. Like if you changed ARCUS.IDGRP to a non-existing customer group code. But if it's something safe like like fixing ARCUS.ADDRESS1, then go for it.

 

[Bluejay07]

Thank you for the advice to both of you. Your knowledge and wisdom is greatly appreciated.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[ettienne]

I agree with Tuba, I use update queries because I have been doing this for a long time, but never recommend it to anyone.

 

[Bluejay07]

I'm now using both methods: view browse and CS browse.
It seems to be working except when accessing the query fields.

l_strSQL = "SELECT EMPLOYEE, EARNDED FROM UPEMPD WHERE EMPLOYEE = " & UPEMPD.Fields("EMPLOYEE").value & " AND (EARNDED LIKE 'OT%')"

ceotDBLINK.OpenView "CS0120", CSQuery
CSQuery.Browse l_strSQL, True
      
Do While CSQuery.Fetch

[COLOR=red]*** ERROR OCCURS HERE ***[/color]
   If Trim$(CSQuery.Fields("EARNDED").value) = "OT1" Then

Using the immediate window, I get:
Run-time error '-2147467259 (80004005)':
Automation Error
Unspecified Error

My actual program error handler shows:
Field 'EARNDED' is not a valid field name.

Can anyone see what I may be doing wrong?
Thanks.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[Jay Converse]

When using CS0120, make sure you do the .InternalSet before .Browsing.

 

[Bluejay07]

Ahh, I missed that. Thank you. I'll try that.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[ettienne]

SELECT EMPLOYEE, EARNDED FROM UPEMPD WHERE EMPLOYEE = '" & UPEMPD.Fields("EMPLOYEE").value & "' AND (EARNDED LIKE 'OT%')

 

[Bluejay07]

Still paying attention to the warnings from earlier, how would I execute an update using CS0120.

Essentially, I am opening the view, creating a second connection using CS0120 and a select statement then I want to update a value.

Here is some abriviated code I am using:

ceotDBLINK.OpenView "UP0008", UPEMPD
UPEMPD.Browse "EARNDED = SAL", 1         '// SAL = SALARY."
Do While UPEMPD.Fetch
   
   l_strSQL = "SELECT EARNDED, ERATE FROM UPEMPD WHERE EMPLOYEE = '" & UPEMPD.Fields("EMPLOYEE").value & "' AND (EARNDED LIKE 'OT%')"

   ceotDBLINK.OpenView "CS0120", CSQuery
   CSQuery.Browse l_strSQL, True
   CSQuery.InternalSet (256)
      
   Do While CSQuery.Fetch
      [COLOR=red]ERROR[/color]
      If Trim$(CSQuery.Fields("EARNDED").value) = "OT1" Then
         CSQuery.Fields("ERATE").value = UPEMPD.Fields("ERATE").value * 24
         CSQuery.Update
            
         'l_strSQL = "UPDATE UPEMPD SET ERATE = " & UPEMPD.Fields("ERATE").value * 24 & " WHERE EMPLOYEE = '" & UPEMPD.Fields("EMPLOYEE").value & "' AND (EARNDED = 'OT1')"

The error I receive with my internal error handler is: Invalid field index: 2.

Since the change only affects one field, data integrety should be alright. I could save the values after each execution and create an addition loop with the actual view for updating, however, I am trying avoid a proccedure with countless loops that could be very time consuming.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[Jay Converse]

You have to open a new copy of the CS0120 view, and run your SQL update from there.

FWIW, if you use the normal UP0008 view, you can .Browse and .Update in the same loop.

 

[Bluejay07]

Tuba,
Judging by your opinion, it seems like I have to have a third copy open regardless for what I want to accomplish.

If I open a third copy for the update, what command is needed since I'm sure .browse doesn't really work with an sql update command.

In addition, a third copy for the UP0008 would work since I would have all the required info from the previous instances: empl. #, salary and earnings code. In this case, I would open it, update and close it before the next loop section.

Overall, my problem is because of the various codes stored in 'EARNEDED' that are required for various uses.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[Jay Converse]

CS0120 is a special view, .Browse does updates. Here's a snippet from some live code of mine:

Dim csTempUpdate As AccpacView
Dim sSQL as string

sSql = "UPDATE ARIBH SET ORDRNBR = '" & csTemp.Fields("ORDNUMBER") & "', IDSHIPNBR = '" & csTemp.Fields("LASTSHINUM") & "'"
sSQL = sSQL & " WHERE CNTBTCH = " & ArInvoiceHeader.Fields("CNTBTCH") & " AND CNTITEM = " & ArInvoiceHeader.Fields("CNTITEM")
csTempUpdate.Browse sSQL, True

 

[Bluejay07]

Thanks.
Another thing I learned about CS0120.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[Bluejay07]

I'm still having problems. I decided to go with the actual view for the third copy. I am not getting any errors, although the correct values are being referenced but the database is not updating. I have tried .browse/.fetch and .read, neither made a difference. I have also tried .PutWithoutVerification, .Process and I've also had the CSQuery.close before the update in case of conflict holding onto the same information.

Any other suggestions would be appreciated.

Do While CSQuery.Fetch
   If Trim$(CSQuery.Fields("EARNDED").value) = "OT1" Then
      ceotDBLINK.OpenView "UP0008", UPEMPD2
      'UPEMPD2.Browse "EMPLOYEE = " & UPEMPD.Fields("EMPLOYEE").value & " AND EARNDED = OT1", 1
      'If UPEMPD2.Fetch Then
      UPEMPD2.Fields("EMPLOYEE") = UPEMPD.Fields("EMPLOYEE").value
      UPEMPD2.Fields("EARNDED") = "OT1"
      If UPEMPD2.Read Then
         UPEMPD2.Fields("ERATE").value = finRound(UPEMPD.Fields("ERATE").value * 24 / 260 / 8 * 1, 2)
         UPEMPD2.Update
      End If
      UPEMPD2.Close
            
      
   End If
Loop
      
l_strSQL = ""
CSQuery.Close

If at first you don't succeed, then sky diving wasn't meant for you!

 

[Bluejay07]

Since the database was not updating, I thought that I might need to compose the views. As soon as I did that, the first process is no longer fetching.

'// Open the views.
ceotDBLINK.OpenView "UP0014", UPEMPL
ceotDBLINK.OpenView "UP0008", UPEMPD
ceotDBLINK.OpenView "UP0010", UPEMPT
ceotDBLINK.OpenView "UP0053", UPEMPC
ceotDBLINK.OpenView "UP0201", UPCHKE
ceotDBLINK.OpenView "UP0062", UPEMTF
ceotDBLINK.OpenView "UP0122", UPEMPO
ceotDBLINK.OpenView "UP0125", UPEMDO
ceotDBLINK.OpenView "UP0126", UPEMTO
ceotDBLINK.OpenView "UP0041", UPEMDB
   
'// Compose the views.
UPEMPL.Compose Array(UPEMPD, UPEMPT, UPEMPC, UPCHKE, UPEMPO)
UPEMPD.Compose Array(UPEMPL, UPEMDO, UPEMDB)
UPEMPT.Compose Array(UPEMPL, UPEMTF, UPEMTO)
UPEMPC.Compose Array(UPEMPL)
UPCHKE.Compose Array(UPEMPL)
UPEMTF.Compose Array(UPEMPT)
UPEMPO.Compose Array(UPEMPL)
UPEMDO.Compose Array(UPEMPD)
UPEMTO.Compose Array(UPEMPT)
UPEMDB.Compose Array(UPEMPD)   
            
'ceotDBLINK.OpenView "UP0008", UPEMPD
UPEMPD.Browse "EARNDED = SAL", 1          '// SAL = SALARY."
   
Do While UPEMPD.Fetch
   '*** THIS SECTION IS NO LONGER EXECUTING ***
   If ArrayHasElements(ArrPtr(m_udtSalInfo())) Then
      ReDim Preserve m_udtSalInfo(UBound(m_udtSalInfo) + 1) As SalaryInfo
   Else
      ReDim m_udtSalInfo(0) As SalaryInfo
   End If
      
   m_udtSalInfo(UBound(m_udtSalInfo)).Employee = UPEMPD.Fields("EMPLOYEE").value
   m_udtSalInfo(UBound(m_udtSalInfo)).Salary = UPEMPD.Fields("ERATE").value
      
Loop

If at first you don't succeed, then sky diving wasn't meant for you!