Send data from Access to another app using VBA & COM

[Andrew Fiddian-Green]

Please excuse my total lack of knowledge but honestly I have never touched
Access before in my life...

I have an own written (exe) application for making price quotations; it has
an automation server interface that allows one to create a price quotation
and add items to it via COM calls. Until now I only have experience of doing
this from a VBA script in Excel that transfers data from spreadsheet cells
into my application. But now I also need to create a similar VBA script for
an Access application to transfer data base fields in a similar way...

The following is the code from my Excel VBA script with some pseudo code
showing what (I think) I need do with Access:

++++

' create application server instance
Dim quoteServer As Object
Set quoteServer = CreateObject("Quotation.Server")

' create a new price quotation
Dim quote As Object
Set quote = quoteServer.CreateQuote

Dim partNo as string
Dim itemQty as string

' pseudo code: select the database
select db = database "abc"

' pseudo code: iterate thru each row of the database
For row = db.rowMin To db.rowMax

' pseudo code: get the part number
select db.row.field.name = "PartNumber"
partNo = db.row.field.value

' pseudo code: get the quantity
select db.row.field.name = "Quantity"
itemQty = db.row.field.value

If (partNo <> "") And (itemQty <> "") Then

' insert part number and quantity into quotation
quote.AddItem partNo & ";" & itemQty

End if

Next row

+++

My four specific questions are how should the real VBA code look for

1) the select db = database "abc"
2) the For row = db.rowMin to db.rowMax / Next iteration
3) the "select db.row.field.name = something" selection
4) the "something = db.field.value" assignment

Any help would be appreciated...

Regards,
AndrewFG

 

[John Nurick]

Hi Andrew,

My four specific questions are how should the real VBA code look for

1) the select db = database "abc"

There may be a terminological issue here. An Access database typically
contains multiple tables, queries, forms, modules etc. I think here
you're thinking of specifying one of the tables (equivalent to the
contents of a single .dbf file).

If so

2) the For row = db.rowMin to db.rowMax / Next iteration
3) the "select db.row.field.name = something" selection
4) the "something = db.field.value" assignment

is along these lines:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDatabase()
Set rs = db.OpenRecordset( "SELECT MyField FROM MyTable;", _
dbOpenForwardOnly)

Do Until rs.EOF
something = rs.Fields(0).Value
' or rs.Fields("MyField").Value
rs.MoveNext
Loop

rs.Close

but you should also check out the DLookup() function.


You don't need to use Access to get data out of an Access table. Here's
an Excel VBA sub as an illustration:

Sub GetData34(Keys As Range, Values As Range, _
DatabaseName As String, Table As String, _
KeyField As String, ValueField As String, _
KeyFieldType As String)

'Works through all the cells in Keys, looking up
'each value in Table.KeyFieldName
'grabbing the value of ValueField in the same record,
'and placing it in the
'corresponding cell in Values.

'Warning: not yet thoroughly tested. As it stands
'will fail on text keys that contain apostrophes.

'Some of this (but not the buggy bits) is based on
'Allen Browne's ELookup() function.

Dim dbEngine As Object 'DAO.dbEngine
Dim db As Object 'DAO.Database
Dim rs As Object 'DAO.Recordset
Dim strSql As String
Dim strKeyValue As String
Dim j As Long

'Open database
Set dbEngine = CreateObject("DAO.DBEngine.36")
Set db = dbEngine.OpenDatabase(DatabaseName)

For j = 1 To Keys.Cells.Count
'Build the SQL string.
strSql = "SELECT TOP 1 [" & ValueField & "] FROM [" _
& Table & "] WHERE [" & KeyField & "] = "
Select Case LCase(KeyFieldType)
Case "string", "text", "memo"
strKeyValue = "'" & Keys.Cells(j).Value & "'"
Case "date", "time", "date/time"
strKeyValue = "#" & Format(Keys.Cells(j).Value, _
"mm/dd/yyyy") & "#"
Case Else
strKeyValue = CStr(Keys.Cells(j).Value)
End Select
strSql = strSql & strKeyValue & ";"
Set rs = db.OpenRecordset(strSql, dbOpenForwardOnly)
If rs.RecordCount = 0 Then
Values.Cells(j).Formula = ""
Else
Values.Cells(j).Formula = rs.Fields(0).Value
End If
rs.Close
Next j

Exit_GetData34:
Set rs = Nothing
db.Close
Set db = Nothing
Set dbEngine = Nothing
Exit Sub

Err_GetData34:
MsgBox "Error in GetData34 at row " & j & ". " & vbCrLf _
& "Error " & Err.Number & ": " & Err.Description, _
vbOKOnly + vbExclamation, "Database lookup"
Resume Exit_GetData34
End Sub

 

[Andrew Fiddian-Green]

John, thanks for the feedback.

I am not sure that I understand the SELECT FROM command in the following:

Set rs = db.OpenRecordset( "SELECT MyField FROM MyTable;", _
dbOpenForwardOnly)

I want to open a particular "Bill of Materials" table and extract two field
values from each entry. Unfortunately your code "SELECT MyField" implies
that I could only read one field value at a time. Therefore I suppose I need
to do something like the following.

Set rs = db.OpenRecordset(<<select Bill of Materials table>>,
dbOpenForwardOnly)

Do Until rs.EOF
partNumber = rs.Fields("PartNumber").Value
itemQuantity = rs.Fields("Quantity").Value
...
rs.MoveNext
Loop

=> Can you please advise the command string that I would need to put between
the <<...>> in the code above?

Regards,
AndrewFG

 

[John Nurick]

Hi Andrew,

If you want to include more than one field in the recordset, you just
add the field name(s) to the query:

SELECT MyField, MyOtherField FROM MyTable;

and take it from there.

But I don't quite know what you mean by "a particular 'Bill of
Materials' table". Do you mean you have multiple Bill of Materials
tables and your code needs to choose the right one? If so, what are the
criteria.

 

[Andrew Fiddian-Green]

Thanks for the help. It is much appreciated.

But I don't quite know what you mean by "a particular 'Bill of
Materials' table". Do you mean you have multiple Bill of Materials
tables and your code needs to choose the right one? If so, what are the
criteria.

I am working with pricing calculation application (written by a third party)
which uses as inputs a price list database, a customer address database and
some product application / configuration rules. It generates as output a
quotation (bill of materials). I have to extend this application with a new
function that will extract data (part number & quantity) from such a bill of
materials file, and send it (using COM) into another independent application
server. Therefore (in answer to your question) I need to use the currently
open bill of materials file...

Regards,
AndrewFG