Connecting to Access thru ODBC 
Author Message
 Connecting to Access thru ODBC

Dear All,

        I am new to Visual Basic(but have a good foundation in SQL) and am
trying to connect to a ODBC. HOwever when I cut and paste codes from the
Microsoft Visual Basic Books online help it gave me error like "database
not define" or "workspace not define" "connection not define" etc.. for
the "dim" statement?

What's wrong with these codes I copied from the example?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Dim wks as Workspace, strSQL As String
Dim cnn As Connection, qdf As QueryDef
Dim strConnect As String, rst As Recordset

Set wks = DBEngine.CreateWorkspace( _
"ODBC", "sa", "", dbUseODBC)
strConnect = "ODBC;DSN=Pubs;UID=SA;PWD=;DATABASE=Pubs"
Set cnn = wks.OpenConnection("", dbDriverNoPrompt, _
False, strConnect)
Set qdf = cnn.CreateQueryDef("Delete94Sales")
strSQL = "DELETE * FROM sales WHERE ord_date BETWEEN "
strSQL = strSQL & "'1/1/1994' AND '12/31/1994'"
qdf.SQL = strSQL

' Run the query asynchronously
qdf.Execute dbRunAsync

' Additional code executes here while the
' query runs...

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

        Also, is it better to use ADO (heard it is suppose to be most effient
way to code).
And is there a kind soul out there willing to share his codes on how to
(1)establish a connection thru ODBC using the ADO method and
(2)manipulate the recordsets returned?  (like using movenext eg..)

        Or is there a good site where these examples are freely available?

        How any good samaritan can kindly provide invaluable help. Thanks!

Regards,
Samuel Tan
Singapore



Fri, 02 Feb 2001 03:00:00 GMT  
 Connecting to Access thru ODBC
Hi Samuel,

you have to include a reference to DAO in your project. PROJECT|REFERENCES
search for Microsoft DAO 3.5 Object Library.
You' re right, if using Access better use DAO direct (dbUseJet) or ADO
instead of ODBC. By the way, the example you posted is using SQLServer not
Access to connect via ODBC. I think www.microsoft.com/DATA is a good
entrypoint for further informations.

Hope this helps,
Hartmut Anders


Quote:
> I am new to Visual Basic(but have a good foundation in SQL) and am
>trying to connect to a ODBC. HOwever when I cut and paste codes from the
>Microsoft Visual Basic Books online help it gave me error like "database
>not define" or "workspace not define" "connection not define" etc.. for
>the "dim" statement?
>Dim wks as Workspace, strSQL As String
>Dim cnn As Connection, qdf As QueryDef
>Dim strConnect As String, rst As Recordset

>Set wks = DBEngine.CreateWorkspace( _
>"ODBC", "sa", "", dbUseODBC)
>strConnect = "ODBC;DSN=Pubs;UID=SA;PWD=;DATABASE=Pubs"
>' Additional code executes here while the
>' query runs...

> Also, is it better to use ADO (heard it is suppose to be most effient
>way to code).



Sat, 03 Feb 2001 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Can you connect to Access thru ODBC?

2. CONNECTING TO ACCESS DB THRU ODBC WITHOUT DSN REMOTLY

3. Connecting to Oracle via VB thru ODBC

4. Connect Informix database thru ODBC driver using Visual Basic

5. Using the COUNT function in Access accessing db2 tbls thru ODBC

6. Reusing VBA/Access Forms thru DAO/ODBC/ADO

7. Problem accessing Oracle thru ODBC from VB 5.0

8. Help: Access Sybase thru ODBC?

9. VB3 using Access 2.0 db thru ODBC

10. VB 5.0 Setup to access AS/400 thru ODBC

11. Crystal Reports accessing flat files thru ODBC

12. Help Connection to Access thru ODBC using DAO

 

 
Powered by phpBB® Forum Software