By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,806 Members | 1,506 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,806 IT Pros & Developers. It's quick & easy.

Make a simple select query

stephane
P: 35
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
Expand|Select|Wrap|Line Numbers
  1. Dim WorkBase as Database 
  2. Dim WorkRS1 as Recordset 
  3. Dim SQL as String 
  4.  
  5. Set WorkBase = OpenDataBase(“voyages.mdb”) 
  6.  
  7. SQL = “Select init from Managers WHERE contact=” & manager 
  8. Set WorkRS1 = WorkBase.OpenRecordset(SQL) 
  9.  
  10. WorkRS1.Close 
  11. WorkBase.Close
  12.  
it returns error at the first line Dim WorkBase as Database
"User-defined type not defined"

what's wrong?
help me pls
Feb 15 '07 #1
Share this Question
Share on Google+
6 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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
Expand|Select|Wrap|Line Numbers
  1. Dim WorkBase as Database 
  2. Dim WorkRS1 as Recordset 
  3. Dim SQL as String 
  4.  
  5. Set WorkBase = OpenDataBase(“voyages.mdb”) 
  6.  
  7. SQL = “Select init from Managers WHERE contact=” & manager 
  8. Set WorkRS1 = WorkBase.OpenRecordset(SQL) 
  9.  
  10. WorkRS1.Close 
  11. WorkBase.Close
  12.  
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
Feb 15 '07 #2

stephane
P: 35
i turn on it,
now, other error at line
Set WorkRS1 = WorkBase.OpenRecordset(SQL)
"Runtime error 13, Type Mismatch"
Feb 15 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
If manager is a control on your form and a text data type, try ...

Expand|Select|Wrap|Line Numbers
  1. SQL = "Select init from Managers WHERE contact='" & manager & "'"
Mary
Feb 15 '07 #4

ADezii
Expert 5K+
P: 8,636
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
Expand|Select|Wrap|Line Numbers
  1. Dim WorkBase as Database 
  2. Dim WorkRS1 as Recordset 
  3. Dim SQL as String 
  4.  
  5. Set WorkBase = OpenDataBase(“voyages.mdb”) 
  6.  
  7. SQL = “Select init from Managers WHERE contact=” & manager 
  8. Set WorkRS1 = WorkBase.OpenRecordset(SQL) 
  9.  
  10. WorkRS1.Close 
  11. WorkBase.Close
  12.  
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.
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database, MyWks As DAO.Workspace
  2. Dim MyRS As DAO.Recordset, strSQL As String
  3.  
  4. strSQL = "SELECT * FROM tblEmployee;"
  5.  
  6. Set MyWks = CreateWorkspace("", "admin", "", dbUseJet)
  7. Set MyDB = MyWks.OpenDatabase("C:\Test\Test.mdb", True)     'Open Exclusive
  8. Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
  9.  
  10. Do While Not MyRS.EOF
  11.   Debug.Print MyRS![FirstName] & " " & MyRS![LastName]
  12.   MyRS.MoveNext
  13. Loop
  14.  
  15. MyRS.Close
  16. MyDB.Close
  17. MyWks.Close
Feb 16 '07 #5

stephane
P: 35
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database, MyWks As DAO.Workspace
  2. Dim MyRS As DAO.Recordset, strSQL As String
  3.  
  4. strSQL = "SELECT * FROM tblEmployee;"
  5.  
  6. Set MyWks = CreateWorkspace("", "admin", "", dbUseJet)
  7. Set MyDB = MyWks.OpenDatabase("C:\Test\Test.mdb", True)     'Open Exclusive
  8. Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
  9.  
  10. Do While Not MyRS.EOF
  11.   Debug.Print MyRS![FirstName] & " " & MyRS![LastName]
  12.   MyRS.MoveNext
  13. Loop
  14.  
  15. MyRS.Close
  16. MyDB.Close
  17. 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
Feb 16 '07 #6

ADezii
Expert 5K+
P: 8,636
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:
Expand|Select|Wrap|Line Numbers
  1. Set MyWks = CreateWorkspace("", "admin", "", dbUseJet)
Feb 16 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.