DMin external database

[Beginner]

Hi!

I would like to use the DMin function to get one piece of information from
an external database. I need to find the smallest date from a table in the
database so that I can do my own queries based on that date.

My problem is that I have no idea how to do this. I am thinking that I
should open the database using OpenDatabase-method, but after this I don't
know what to do. I tried to open the table into a recordset, but it failed.

My code so far:
Dim rs1 As ADODB.Recordset
Dim db As Database
Dim d As String
Set db = OpenDatabase("TheDatabase")
Set rs1 = db.OpenRecordset("TheTable") //this doesn't work
'and then I would do like
d = DMin("[TheField]", rs1)

Help is appreciated

-Beginner-

 

[Douglas J. Steele]

OpenDatabase is a DAO method, so you must use a DAO recordset.

Try:

Dim rs1 As DAO.Recordset
Dim db As DAO.Database
Dim d As String
Dim strSQL As String

strSQL = "SELECT Min([TheField]) FROM [TheTable])

Set db = OpenDatabase("TheDatabase")
Set rs1 = db.OpenRecordset(strSQL)
d = rs1.Fields(0)
rs1.Close
Set rs1 = Nothing
Set db = Nothing

 

[Alex Dybenko]

Hi,
you can also make a query, which will get table from external database using
IN clause and then use it in DMIN()

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Beginner]

Thank you both for answers! I used Douglas's method and it did exactly what I
wanted.

Beginner

"Alex Dybenko" kirjoitti:

Hi,
you can also make a query, which will get table from external database using
IN clause and then use it in DMIN()

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Hi!

I would like to use the DMin function to get one piece of information from
an external database. I need to find the smallest date from a table in the
database so that I can do my own queries based on that date.

My problem is that I have no idea how to do this. I am thinking that I
should open the database using OpenDatabase-method, but after this I don't
know what to do. I tried to open the table into a recordset, but it
failed.

My code so far:
Dim rs1 As ADODB.Recordset
Dim db As Database
Dim d As String
Set db = OpenDatabase("TheDatabase")
Set rs1 = db.OpenRecordset("TheTable") //this doesn't work
'and then I would do like
d = DMin("[TheField]", rs1)

Help is appreciated

-Beginner-