Tuesday
Aug072007

Create a connection to DB: VBA Access

DAO example - ODBC Connection

Dim ws as Workspace

dim db as database

dim strConnection as string

 
set ws=DBEngine.Workspaces(0)

let strConnection= "ODBC;DSN=" & DatabaseName & ";UID=" & UserName

& ";PWD=" & UserPassword

set db=ws.OpenDatabase("", False, False, strConnection)

ADO Example

Dim ad as ADODB.Connection

 set ad=New ADODB.Connection

Let ad.ConnectionString= "ODBC;DSN=" & DatabaseName & ";UID=" &

UserName & ";PWD=" & UserPassword

ad.Open
Tuesday
Aug072007

Open Table Query for viewing: VBA Access

DAO Example

Dim rs as recordset

set rs=db.openrecordset({tablename or SQL})

do while not rs.eof

  'Put the code here for what to do with the information.

  'The field information can be access by the field name

  intID=rs!IDField

  'Or by the order number it is in the list (starting at 0)

  intString=rs.Field(1)

  rs.movenext

loop

ADO example

dim ar as ADODB.recordset

set ar=new adodb.recordset

ar.open {SQL Statement}

do while not ar.EOF

  'Put the code here for what to do with the information.

  'The field information can be access by the field name

  intID=ar!IDField

  'Or by the order number it is in the list (starting at 0)

  intString=ar.Field(1).value

  ar.movenext

loop