General adivice - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

General adivice

Author

Topic

johnpapa
Starting Member

7 Posts

Posted - 2012-12-17 : 03:44:29

Being new to SQL Server, I am trying to take some decisions as to how to proceed. For the time being I intend to use Access as a front end and vba for programing.
I have looked into ODBC (DSN and DSN-less) and I am leaning towards using ADO with the SQL Native Client 11.0. Typical code for executing a simple SP is indicated below.

Do you have a any general pointers based on your experience that might save time and effort? Thanks.

Dim con As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command

'OLE DB (ADO) Native CLient
con.ConnectionString = "Provider=SQLNCLI11;" _
         & "DataSource=MSSQLSERVER;" _
         & "Server=(local);" _
         & "Database=JP1;" _
         & "Uid=sa;" _
         & "Pwd=p5p6p7p8;" _
         & "DataTypeCompatibility=80;" _
         & "MARS Connection=True;"
con.Open
con.CursorLocation = adUseClient
con.CommandTimeout = 0

Set cmd = New ADODB.Command
With cmd
    .ActiveConnection = con
    .CommandText = "JPSP4"
    .CommandType = adCmdStoredProc
    .Parameters.Refresh
    .Parameters("@lngNum").Value = 2
End With

Set rst = cmd.Execute

Set Me.Recordset = rst

con.Close
Set con = Nothing
Set cmd = Nothing
Set rst = Nothing

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources