Access connecting to SQL Server

Hello Forum,

I am attempting to utilize an Access db to view/report/analyze data from a few SQL Server Databases.
I know that you can connect via ODBC but I have several workstations/remote desktop logins that may be using this database.

Does anyone know any other ways (ADO?) to connect to a SQL Server database and if so can it be automatically programmed
login or workstation?

Thank you for your time and in advance for any help you may be able to toss my way.....

t_neustel

'XXXXXXXXXXXXXXXXXXXXXXXXX_CREATE~CONNECTION_XXXXX XXXXXXXXXXXXXXXXXXXXXX
Global gconMyConnection As ADODB.Connection
Global Const gstrMyConnectionString = "Provider=sqloledb;" & _
"Data Source=(SQL_SERVER_NAME_GOES_HERE);" & _
"Initial Catalog=SQL_SERVER_DATABASE_NAME_GOES_HERE;" & _
"Integrated Security=SSPI;"
Set gconMyConnection = New ADODB.Connection
With gconMyConnection
.ConnectionString = gstrMyConnectionString
.CursorLocation = adUseServer
.Open
End With
'XXXXXXXXXXXXXXXXXXXXX_USE~CONNECTION~STORED~PROCE DURE_XXXXXXXXXXXXXXXXXXX
Dim pstrSQL As String
Dim pADOCommand As New ADODB.Command

' Run SQL_Server stored procedure from access
With pADOCommand
.ActiveConnection = gconMyConnection
.CommandType = adCmdStoredProc
.CommandText = "My_Stored_Procedure"
.Parameters.Refresh
.Parameters(1) = MyVariable#1
.Parameters(2) = MyVariable#2 etc...
.Execute
End With
'XXXXXXXXXXXXXXXXXXXXX_USE~CONNECTION~SQL~RECORDSE T_XXXXXXXXXXXXXXXXXXX
' Set SQL statement for ADO Query Recordset
pstrSQL = "SELECT TheData " & _
"FROM ThisTable " & _
"GROUP BY This, That, TheOther " & _
"ORDER BY This"

Set grsTimeSheets = New ADODB.Recordset
With grsTimeSheets
.ActiveConnection = gconMyConnection
.LockType = adLockOptimistic
.CursorType = adOpenDynamic
.Source = pstrSQL
.Open
If .EOF And .BOF Then Exit Sub
End With
'XXXXXXXXXXXXXXXXXXXXX_HOPE~THAT~HELPS~_XXXXXXXXXX XXXXXXXXX

'XXXXXXXXXXXXXXXXXXXXX_~SPARKER~_XXXXXXXXXXXXXXXXX XX

Yes...That helps quite a bit...I found the answers as well...but your code seems very explanatory...Thanks!!

Have you ever tried to access a "View" from a SQL Server. I thought this might be the solution because one of the recordsets I am trying to get info from has 7 million records and that wouldn't be good for Access to crunch! So we thought a view from the SQL Server database might do the trick.

THanks again!!

t_neustel