Re: ODBC Help

From: Dan Ross <daniel.a.ross_at_boeing.com>
Date: Fri, 27 Aug 1999 21:13:48 GMT
Message-ID: <FH56yJ.L85@news.boeing.com>

Anderson:

The problem is you are Using the JET Workspace object and you need to be = access the ODBC Direct Type. Try this

---

Sub Test()
Dim wsODBC As Workspace
Dim dbs As Database, rst As Recordset
Dim strsql, strconnect As String     

    strconnect = "ODBC;DSN=PROD;"     

Set wsODBC = DBEngine.CreateWorkspace("ODBCws", "", "", dbUseODBC)

                       DBEngine.Workspaces.Append wsODBC 'Add the =
Workspace to the Workspaces Collection
             

Set dbs = wsODBC.OpenDatabase(strconnect, dbDriverComplete, False, = strconnect)     

        strsql = "Select * from dct_test;" Set rst = dbs.OpenRecordset(strsql, , dbOpenSnapshot + = dbSQLPassThrough) ' <== This is where is bombs.

stop

wsODBC.Close
End sub

---

• xdar

  Anderson <tande922_at_hotmail.com> wrote in message = news:37C6EAA2.38B8C9B7_at_hotmail.com...
  Here is my problem.
  I'm trying to execute a select query using DAO in VBA.   Private Sub test()
  Dim dbs As DataBase, rst As Recordset   Dim strsql, strconnect As String

  strconnect = "ODBC;DSN=PROD;"
  Set dbs = OpenDatabase("", dbDriverPrompt, False, strconnect)   strsql = "Select * from dct_test;"
  Set rst = dbs.OpenRecordset(strsql, , dbSQLPassThrough) <== This = is where is bombs.

  End Sub

  I keep getting the error microsoft jet can't find the table or query = 'username.dct_test'.
  I know that the table is there because I can insert into it from code. =

  IE
  strsql = "Begin Insert into DCT_TEST (TEST) values ('Test'); End;"   dbs.execute strsql, dbsqlPassThrough

  All I really want to do is populate a DB Grid with the results from my = recordset.
  What am I doing wrong.
  My VBA Skills are OK but I'm a DBA trying to code so you will have to = excuse me if my code looks a bit funny.   Any help would be great.

  TIA,   Terry Received on Fri Aug 27 1999 - 16:13:48 CDT