Create ODBC connection without DSN 
Author Message
 Create ODBC connection without DSN

Hello,

I am trying to create a connection to a SQL Server 7 database using DAO.  In
this case I can not use a DSN

I tried the following, but I get the error: "Invalid connection string in
pass-through query

Sub RunPassthrough()

    Dim dbFlash As DAO.Database, qdFlash As DAO.QueryDef
    Dim strParm00 As String, strParm01 As String
    Dim strParm02 As String, strParm03 As String

    strParm01 = "Customer"
    strParm02 = "PriceList"
    strParm00 = "'" & strParm01 & "', " & "'" & strParm02 & "'"

    Set dbFlash = DBEngine.Workspaces(0).Databases(0)
    Set qdFlash = dbFlash.CreateQueryDef("")
    qdFlash.ReturnsRecords = False
    qdFlash.Connect = "Driver={SQL
Server};Server=MyServer;uid=Test;pwd=test123;Database=dbMySS7"

    qdFlash.SQL = "exec sp_web_OIcfg_Customer " & strParm00
    qdFlash.Execute
    qdFlash.Close



Sat, 14 Feb 2004 01:26:17 GMT  
 Create ODBC connection without DSN
Why do you want to use DAO? You should use ADO to connect to SQL. DAO is
extremely inefficient. See <www.able-consulting.com> for examples of
creating ADO connection strings using OLEDB to connect to SQL Server.

HTH,
Bob Barrows

Quote:
> Hello,

> I am trying to create a connection to a SQL Server 7 database using DAO.
In
> this case I can not use a DSN

> I tried the following, but I get the error: "Invalid connection string in
> pass-through query

> Sub RunPassthrough()

>     Dim dbFlash As DAO.Database, qdFlash As DAO.QueryDef
>     Dim strParm00 As String, strParm01 As String
>     Dim strParm02 As String, strParm03 As String

>     strParm01 = "Customer"
>     strParm02 = "PriceList"
>     strParm00 = "'" & strParm01 & "', " & "'" & strParm02 & "'"

>     Set dbFlash = DBEngine.Workspaces(0).Databases(0)
>     Set qdFlash = dbFlash.CreateQueryDef("")
>     qdFlash.ReturnsRecords = False
>     qdFlash.Connect = "Driver={SQL
> Server};Server=MyServer;uid=Test;pwd=test123;Database=dbMySS7"

>     qdFlash.SQL = "exec sp_web_OIcfg_Customer " & strParm00
>     qdFlash.Execute
>     qdFlash.Close



Sat, 14 Feb 2004 02:11:42 GMT  
 Create ODBC connection without DSN
Steven,

You need to prefix your connect string w/ 'ODBC;'

As per your example:
ODBC;Driver={SQL
Server};Server=MyServer;uid=Test;pwd=test123;Database=dbMySS7

BTW, with regards to your other post.  DAO, in the fashion you are using it
(ODBC Direct/pass-through queries), is quite efficient.  It is fair to say
that ADO/OLE DB is more flexible.  It is not fair to say that it is not
efficient or even worse 'extremely inefficient'.

-Ted McNeal


Quote:
> Hello,

> I am trying to create a connection to a SQL Server 7 database using DAO.
In
> this case I can not use a DSN

> I tried the following, but I get the error: "Invalid connection string in
> pass-through query

> Sub RunPassthrough()

>     Dim dbFlash As DAO.Database, qdFlash As DAO.QueryDef
>     Dim strParm00 As String, strParm01 As String
>     Dim strParm02 As String, strParm03 As String

>     strParm01 = "Customer"
>     strParm02 = "PriceList"
>     strParm00 = "'" & strParm01 & "', " & "'" & strParm02 & "'"

>     Set dbFlash = DBEngine.Workspaces(0).Databases(0)
>     Set qdFlash = dbFlash.CreateQueryDef("")
>     qdFlash.ReturnsRecords = False
>     qdFlash.Connect = "Driver={SQL
> Server};Server=MyServer;uid=Test;pwd=test123;Database=dbMySS7"

>     qdFlash.SQL = "exec sp_web_OIcfg_Customer " & strParm00
>     qdFlash.Execute
>     qdFlash.Close



Sat, 14 Feb 2004 03:45:13 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Crystal and ODBC DSN/DSN-less connections - do I need USER DSN or SYSTEM DSN

2. Creating ODBC System DSN by using ODBC API ?

3. crystal report using ODBC without a DSN

4. Using dBase IV with VB via ODBC without a DSN

5. CONNECTING TO ACCESS DB THRU ODBC WITHOUT DSN REMOTLY

6. DSN-less Connection without WYSIWYG program, fist time...

7. password connection without DSN for jet/MSAccess 2000

8. Run Report Without a Dsn Connection

9. password connection without DSN for jet/MSAccess 2000

10. Import Excel with ODBC Drivers and DSN-less connection

11. odbc fil dsn oracle 8i connection won't work

12. How to establish odbc connection with DSN?

 

 
Powered by phpBB® Forum Software