VB password entry does not allow queries to be run

M

Mason

When I connect automatically in VB using Open () method of the
ADODB.Connection to an SQL server via Access, I get positive tests that the
connection is active, but I cannot run queries without having to reconnect.
Do I need to convert all of my queries to SQL? What am I missing?
 
M

Mason

Graham:

I run the query immediately following my connection via macro. Here is
sample of the code. Do I need to dim my DB or queries in any way?

Public Sub ConnectionString()

Dim cnn1 As ADODB.Connection

Set cnn1 = New ADODB.Connection
cnn1.ConnectionString = "driver={SQL Server};" & _
"server=SERVER;uid=UID;pwd=PW;database=D685UCR"
cnn1.ConnectionTimeout = 30
cnn1.Open

MsgBox "cnn1 state: " & GetState(cnn1.State) & vbCr

End Sub
Public Function GetState(intState As Integer) As String

Select Case intState
Case adStateClosed
GetState = "adStateClosed"
Case adStateOpen
GetState = "adStateOpen"
End Select

End Function

Dim stDocName As String
stDocName = 'YOUR QUERY NAME'
DoCmd.OpenQuery stDocName, acNormal, acEdit
 
D

Douglas J. Steele

You're mixing methods here. The DoCmd.OpenQuery doesn't know anything about
the ADO Connection object you've created.

You could try creating a pass-through query, and set the QueryDef object's
Connect property before using it.
 
M

Mason

Thanks, I was hoping to avoid writing the pass-through queries, but it looks
like the way to go.

Mason
 
G

Graham R Seach

Mason,

In addition to Doug's suggestion, once you have a valid connection, you can
execute a query against the Connection object.

Dim cnn1 As ADODB.Connection
Dim stSQL As String

Set cnn1 = New ADODB.Connection
cnn1.ConnectionString = "driver={SQL Server};" & _
"server=SERVER;uid=UID;pwd=PW;database=D685UCR"
cnn1.ConnectionTimeout = 30
cnn1.Open

stSQL = 'YOUR QUERY SQL'
cnn1.Execute stSQL

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
 
D

Douglas J. Steele

Graham: Does that open the query to show the results in a grid, the way
OpenQuery does? I thought it was only for running action queries.
 
G

Graham R Seach

Hi Doug,

No mate. I sometimes find people use the OpenQuery method because they
aren't aware of other ways of doing it. Mason didn't show us what the rest
of his code was doing, so I thought I'd round out the picture, just in case.
That's why I offered the modified code in addition to your suggestion.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
 
Top