SVicente's Blog

Follow me and you'll know what I like to do

Access – how to open a SQL Server database (VBA example)

'=========================================================================
'
' This VBA code demonstrates how to open a SQL Server database connection
' Author.: svicente99@yahoo.com
' Date...: 2011, Apr.12
' Blog...: svicente99.wordpress.com
' Twitter: @svicente99
'
'=========================================================================

Option Compare Database

Private Const SERVIDOR = "YOURSERVER\SQLEXPRESS"
Private Const BANCO = "BD_TESTE"
Private Const USUARIO = "ubdteste"
Private Const SENHA = "tst"

Public Sub Main()
Call AbreBancoSQL
Call OpenSQLDatabase
End Sub

Private Sub AbreBancoSQL()

Dim strConn As String

On Error Resume Next
Set cn = CreateObject("ADODB.Connection")

strConn = "Provider=SQLOLEDB.1;User ID=" & USUARIO & ";Password=" & SENHA & ";" & _
"Initial Catalog=" & BANCO & ";Data Source=" & SERVIDOR & ";" & _
"Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096"

'MsgBox strConn
'abre efetivamente a conexao com o banco
cn.Open strConn

If Err.Number = 0 Then
MsgBox " Conexao efetuada com sucesso ! "
Else
MsgBox " Erro na abertura do banco " & BANCO & "." & vbCrLf & _
"Detalhes: " & Err.Description
On Error GoTo 0
Err.Clear
End If
End Sub

Private Sub OpenSQLDatabase()

Set cn = New ADODB.Connection

On Error Resume Next
cn.Provider = "SQLNCLI.1"
cn.Properties("Data Source").Value = SERVIDOR
cn.Properties("Initial Catalog").Value = BANCO
cn.Properties("User ID").Value = USUARIO
cn.Properties("Password").Value = SENHA
cn.Open 'effectively open database

If Err.Number = 0 Then
MsgBox " Success! Connection established. "
Else
MsgBox " Error ocurred while trying to open database " & BANCO & "." & vbCrLf & _
"Details: " & Err.Description
On Error GoTo 0
Err.Clear
End If

End Sub


To download an Access database where this example was programmed, just click:
Bd Access 2003 – Modulo 1 – How to open a SQL Server database

April 13, 2011 - Posted by | Programming | , , ,

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: