[AccessD] A97 connection

Jim,

Thanks for this.  I was afraid I was going to have do things this way.  At
least now I have some examples.

John W. Colby
www.colbyconsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Jim Lawrence
(AccessD)
Sent: Sunday, October 26, 2003 9:18 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] A97 connection


Hi John:

I not sure whether this is answering your question but here goes anyway.

A place to start...

Public mobjConn As ADODB.Connection
Public gstrConnection As String

On Error GoTo Err_NotConnected

' if your are connecting to a SQL server
gstrConnection = "Provider=SQLOLEDB; _
		   Initial Catalog=MyDatabaseName; _
		   Data Source=MyServer; _
		   Integrated Security=SSPI" 'Use the M$ security

' or a MDB database
gstrConnection = "Provider=Microsoft.Jet.OLEDB.4.0; _
               Persist Security Info=False; _
               Data Source= MyDatabaseNameAndLocation"

' Test connection string
Set mobjConn = New ADODB.Connection
mobjConn.ConnectionString = gstrConnection
mobjConn.Open
MsgBox "You are connected "

Exit_GetConnected:
 Exit Sub

Err_NotConnected:
 MsgBox "You are not connected "
 Resume Exit_GetConnected

and everytime you want to connect after that...

 Dim cmdInvoice As ADODB.Command
 Public rsInvoice As ADODB.Recordset

 Set rsInvoice = New ADODB.Recordset
 Set cmdInvoice = New ADODB.Command

' accessing a parameterized query in a MDB database
With cmdInvoice
 .ActiveConnection = gstrConnection
 .CommandText = "Myquery"
 .CommandType = adCmdStoredProc
 .Parameters.Append .CreateParameter("InvoiceCode", adChar, adParamInput,
10, sInvoiceCode)
End With

With rsInvoice
 .CursorLocation = adUseClient
 .LockType = adLockBatchOptimistic
 .Open cmdInvoice
 If .EOF = False And .BOF = False Then
  .MoveLast
  GetInvoice = True
 End If
End With

...or direct, no command section needed

With rsInvoice
 .CursorLocation = adUseClient
 .LockType = adLockBatchOptimistic
 .Open "SELECT * FROM Invoice " & _
             "ORDER BY [Group]", gstrConnection
 If .EOF = False And .BOF = False Then
  .MoveLast
  GetInvoice = True
 End If
End With

...or with SQL or Oracle servers use command section

With cmdInvoice
 .ActiveConnection = gstrConnection
 .CommandText = "MyStoredProcedure"
 .CommandType = adCmdStoredProc
 .Parameters.Append .CreateParameter("@InvoiceNumber", adInteger,
adParamInput, , lngInvoiceNumber)
End With

With rsInvoice
 .CursorLocation = adUseClient
 .Open objCmd, , adOpenDynamic, adLockOptimistic	'This is where the
connection types and locks can also be set.
 If .EOF = False And .BOF = False Then
  .MoveLast
  GetInvoice = True
 End If
End With

The previous is a little over-kill but I would not want one small point to
be missed and hold up production. If there are any errors in the previous my
memory and typing skills are no perfect. Note the three types of ADO sets,
recordset, connection and command set.

HTH
Jim

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of John Colby
Sent: Sunday, October 26, 2003 7:52 AM
To: AccessD
Subject: [AccessD] A97 connection


OK, now that I have dimmed a connection object, who do I find an object that
has a connection to set this equal to?  In A2K I just use:

set gcnn = currentproject.connection.

However in A97 AFAICT currentproject doesn't exist.  Am I going to have to
edit the connection information manually?

John W. Colby
www.colbyconsulting.com


_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com