MDB TO MDB

N

Norbert Beliso

Please,
I have a split database. Through coding,
I want the frontend to query a table in
the backend, the result will be just one record
of one field. What is the best way to do this?

I want to use a connection string and not
linked tables, Im going to capture the result
in a variable. Im using Access 2002.

Mahalo.
 
B

Bruce M. Thompson

I have a split database. Through coding,
I want the frontend to query a table in
the backend, the result will be just one record
of one field. What is the best way to do this?

I want to use a connection string and not
linked tables, Im going to capture the result
in a variable. Im using Access 2002.

You can generate a query containing the "IN" clause which you can open as a
recordset via the "OpenRecordset()" method and retrieve the required value (you
can build the sql in code allowing the insertion of the database path and
filename):

'***SAMPLE (Criteria not set)
SELECT OrderID, OrderDate
FROM Orders
IN 'C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb';
'***

Or you can open a recordset using the "OpenDatabase()" method prior to the
"OpenRecordset()" method to retrieve the require value. This would be my
preferred method. The following example requires a reference to the DAO 3.6
Object Library (watch line wrap):

'*******EXAMPLE START
Public Function fGetOrderDateByOrderID(lngOrderID As Long) As Variant
' Comments : Retrieves Order Date from Orders Table in Northwind.mdb
' Location of Northwind.mdb may vary from machine to
machine
' Parameters: lngOrderID - The Order ID
' Returns : Variant - Date (Null if no matching record)
' Created : 10/29/03 18:49 BMT
' Modified :
'
' --------------------------------------------------

On Error GoTo fGetOrderDateByOrderID_ERR

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strDataFile As String
Dim strSQL As String

'Location of Northwind database
strDataFile = _
"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"

'Returns record meeting specified criteria
strSQL = "SELECT ORDERID, OrderDate FROM Orders " _
& "WHERE (OrderID = " & lngOrderID & ");"
'Open the database
Set db = OpenDatabase(strDataFile)
'Open the recordset based on strSQL
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

With rs
If .RecordCount > 0 Then
fGetOrderDateByOrderID = !OrderDate
Else
fGetOrderDateByOrderID = Null
End If
End With

fGetOrderDateByOrderID_EXIT:
'Clean up
On Error Resume Next
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
Exit Function

fGetOrderDateByOrderID_ERR:
MsgBox "Error " & Err.Number & " occurred in fGetOrderDateByOrderID: " &
Err.Description
Resume fGetOrderDateByOrderID_EXIT

End Function
'*******EXAMPLE END

The above example function is called using this syntax (make sure that the
Northwind.mdb path and filename match that on your system):

vReturnValue = fGetOrderDateByOrderID(10248)

You can also use ADO code to perform the same task, but I have no need for ADO,
so I am not versed in the same. <g>

Is this what you were looking for?
 
B

Bruce M. Thompson

Here's that sample function without the linewrap (hopefully):

'**********
Public Function fGetOrderDateByOrderID(lngOrderID As Long) As Variant
' Comments : Retrieves Order Date from Orders Table in Northwind.mdb
' Location of Northwind.mdb may vary from machine to machine
' Parameters: lngOrderID - The Order ID
' Returns : Variant - Date (Null if no matching record)
' Created : 10/29/03 18:49 BMT
' Modified :
'
' --------------------------------------------------

On Error GoTo fGetOrderDateByOrderID_ERR

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strDataFile As String
Dim strSQL As String

strDataFile = _
"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
strSQL = "SELECT ORDERID, OrderDate FROM Orders " _
& "WHERE (OrderID = " & lngOrderID & ");"

Set db = OpenDatabase(strDataFile)
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

With rs
If .RecordCount > 0 Then
fGetOrderDateByOrderID = !OrderDate
Else
fGetOrderDateByOrderID = Null
End If
End With


fGetOrderDateByOrderID_EXIT:
On Error Resume Next
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
Exit Function

fGetOrderDateByOrderID_ERR:
MsgBox "Error " & Err.Number & _
" occured in fGetOrderDateByOrderID: " & Err.Description
Resume fGetOrderDateByOrderID_EXIT

End Function
'**********

:)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top