SVicente's Blog

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

Access – Open and read external table (VBA example)

Module: OpenDB

'================================================================================
'
' This VBA code demonstrates how to open a SQL Server database connection (or an
' MS Access database file) and to iterate through all records of its inner table
' Author.: svicente99@yahoo.com
' Date...: 2011, Apr.13
' Blog...: svicente99.wordpress.com
' Twitter: @svicente99
'
'================================================================================

Option Compare Database

Public Sub Main()
Dim oConn
Dim clDatabases As New Class_Databases
Dim clTables As New Class_Tables

oConn = clDatabases.fOpenSQLDatabase()
oConn = clDatabases.fOpenMDBAccess()
clTables.ReadTable (oConn)
Set oConn = Nothing
Set clDatabases = Nothing
End Sub

Class: Databases

Option Compare Database

Private Const SERVIDOR = "YOUR_SERVER\SQLEXPRESS"
Private Const MDB_FILE = "MDB_Path\bdReward-copy.mdb"
Private Const MDW_FILE = "System.mdw"
Private Const BANCO = "BD_TESTE"
Private Const USUARIO = "ubdteste"
Private Const SENHA = "tst"

Public 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

Public Function fOpenSQLDatabase()

Dim cn
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. (SQL Server)"
fOpenSQLDatabase = cn
Else
MsgBox " Error ocurred while trying to open database " & BANCO & "." & vbCrLf & _
"Details: " & Err.Description
On Error GoTo 0
Err.Clear
End If

End Function

Public Function fOpenMDBAccess()

Dim cn
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Data Source") = MDB_FILE
' .Properties("Jet OLEDB:System database") = MDW_FILE
' .Properties("User ID") = USUARIO
' .Properties("Password") = SENHA
.Properties("Persist Security Info") = False
.Properties("Mode") = adModeShareDenyNone
.Open
End With

If Err.Number = 0 Then
MsgBox " Success! Connection established. (Access) "
fOpenMDBAccess = cn
Else
MsgBox " Error ocurred while trying to open Access " & MDB_FILE & "." & vbCrLf & _
"Details: " & Err.Description
On Error GoTo 0
Err.Clear
End If

End Function

Class: Tables

Option Compare Database

Public Sub ReadTable(oConnection)

Dim nRecord As Integer
Dim str As String, SQL As String
Dim oCommand As New ADODB.Command
Dim oRecordSet As New ADODB.Recordset

SQL = "SELECT * FROM [1-entrada];"

With oCommand
.ActiveConnection = oConnection
.CommandText = SQL
.CommandType = adCmdText
End With

'Defines our RecordSet object.

' .CursorType sets a static cursor, the only choice for a client side cursor
' .CursorLocation sets a client side cursor, the only choice for an Access database
' .LockType sets an optimistic lock type
' .Open executes the cmdCommand object against the data source and stores the
' returned records in our RecordSet object.

With oRecordSet
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open oCommand
End With

nRecord = 1
With oRecordSet
While Not .EOF
str = nRecord & "º record ------------" & vbCrLf
str = str & "ID_FUN: " & .Fields(0) & vbCrLf
str = str & "CODAUS: " & .Fields(1) & vbCrLf
str = str & "GRPAUS: " & .Fields(2) & vbCrLf
str = str & "DTINICIAL: " & .Fields(3) & vbCrLf
str = str & "DTFINAL: " & .Fields(4) & vbCrLf & vbCrLf
str = str & "[ stop w/ Ctrl-Break ]"
MsgBox str
.MoveNext
nRecord = nRecord + 1
Wend
.Close
End With

Set oRecordSet = Nothing
Set oCommand = Nothing

End Sub



To download an Access database where this example was programmed, just click:
Bd Access 2003 – How to open and read a external table

Advertisements

April 14, 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: