Make a simple select query

[stephane]

how?
i need get some data from table, at the action
i try anything DAO and ADO, it doesn't works((

for example, this code

Dim WorkBase as Database 
Dim WorkRS1 as Recordset 
Dim SQL as String 
 
Set WorkBase = OpenDataBase(“voyages.mdb”) 
 
SQL = “Select init from Managers WHERE contact=” & manager 
Set WorkRS1 = WorkBase.OpenRecordset(SQL) 
 
WorkRS1.Close 
WorkBase.Close
 

it returns error at the first line Dim WorkBase as Database
"User-defined type not defined"

what's wrong?
help me pls

[MMcCarthy]

how?
i need get some data from table, at the action
i try anything DAO and ADO, it doesn't works((

for example, this code

Dim WorkBase as Database 
Dim WorkRS1 as Recordset 
Dim SQL as String 
 
Set WorkBase = OpenDataBase(“voyages.mdb”) 
 
SQL = “Select init from Managers WHERE contact=” & manager 
Set WorkRS1 = WorkBase.OpenRecordset(SQL) 
 
WorkRS1.Close 
WorkBase.Close
 

it returns error at the first line Dim WorkBase as Database
"User-defined type not defined"

what's wrong?
help me pls

In the VBA Editor check Tools - References. Make sure there is a DAO library ticked on the list.

Mary

[stephane]

i turn on it,
now, other error at line
Set WorkRS1 = WorkBase.OpenRecordset(SQL)
"Runtime error 13, Type Mismatch"

[MMcCarthy]

If manager is a control on your form and a text data type, try ...

SQL = "Select init from Managers WHERE contact='" & manager & "'"

Mary

[ADezii]

how?
i need get some data from table, at the action
i try anything DAO and ADO, it doesn't works((

for example, this code

Dim WorkBase as Database 
Dim WorkRS1 as Recordset 
Dim SQL as String 
 
Set WorkBase = OpenDataBase(“voyages.mdb”) 
 
SQL = “Select init from Managers WHERE contact=” & manager 
Set WorkRS1 = WorkBase.OpenRecordset(SQL) 
 
WorkRS1.Close 
WorkBase.Close
 

it returns error at the first line Dim WorkBase as Database
"User-defined type not defined"

what's wrong?
help me pls

Here is workable code that Opens an External Database (C:\Test\Test.mdb), creates a Recordset based on a simple SQL Statement, then loops through all Records in the Recordset outputting the First and Last Name Fields to the Immediate Window.

Dim MyDB As DAO.Database, MyWks As DAO.Workspace
Dim MyRS As DAO.Recordset, strSQL As String
 
strSQL = "SELECT * FROM tblEmployee;"
 
Set MyWks = CreateWorkspace("", "admin", "", dbUseJet)
Set MyDB = MyWks.OpenDatabase("C:\Test\Test.mdb", True)     'Open Exclusive
Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
 
Do While Not MyRS.EOF
  Debug.Print MyRS![FirstName] & " " & MyRS![LastName]
  MyRS.MoveNext
Loop
 
MyRS.Close
MyDB.Close
MyWks.Close

[stephane]

Dim MyDB As DAO.Database, MyWks As DAO.Workspace
Dim MyRS As DAO.Recordset, strSQL As String
 
strSQL = "SELECT * FROM tblEmployee;"
 
Set MyWks = CreateWorkspace("", "admin", "", dbUseJet)
Set MyDB = MyWks.OpenDatabase("C:\Test\Test.mdb", True)     'Open Exclusive
Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
 
Do While Not MyRS.EOF
  Debug.Print MyRS![FirstName] & " " & MyRS![LastName]
  MyRS.MoveNext
Loop
 
MyRS.Close
MyDB.Close
MyWks.Close

thanks, this code is works.
i think the reason of error was at the dbOpenDynaset key. i don't assign it to function befor

[ADezii]

thanks, this code is works.
i think the reason of error was at the dbOpenDynaset key. i don't assign it to function befor

The reason why it didn't work before was probably because this line was omitted from the code. You must create a Workspace Object to open a Database within:

Set MyWks = CreateWorkspace("", "admin", "", dbUseJet)