Microsoft Access

Microsoft Access - ConnectionStrings 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
' Access
    Dim acnAccess As ADODB.Connection
    Set acnAccess = CurrentProject.Connection

    sz = "Select DCODE from DISTRICT where GIS_SERV_AREA='" & strDistrictCode & "'"
    Set rsinMisc = New ADODB.Recordset
    rsinMisc.Open sz, acnAccess, adOpenKeyset, adLockOptimistic
    If Not rsinMisc.EOF Then
:

' Oracle
    Const acnPPConnectionStringAPS = "Provider=MSDAORA.1;" _
        & "Password=wruserPassw0rd;User ID=wruser;Data Source=aps"

    Dim acnPP As ADODB.Connection
    Set acnPP = New ADODB.Connection
    acnPP.ConnectionString = acnPPConnectionStringAPS
    acnPP.Open

    Dim strin As String
    strin = "select csm_caseno, case_type, prc_parcel_no, csm_updated, " _
        & "   his_what, his_id, csm_issued_date " _
        & "from aps_admin.yHistory_CaseMain_2_wr " _
        & "where his_processed=0 " _
        & "order by to_number(his_id)"
    
    Dim rsIn As ADODB.Recordset
    Set rsIn = New ADODB.Recordset
    rsIn.Open strin, acnPP, adOpenKeyset, adLockOptimistic
:
    
' SQL Server
    Const acnGISConnectionString = "Provider=SQLOLEDB.1;Password=g;" _
        & "User ID=g;Initial Catalog=GISPROD;Data Source=PWASQLSTAGE"

    Dim acnGIS As ADODB.Connection
    Set acnGIS = New ADODB.Connection
    acnGIS.ConnectionString = acnGISConnectionString
    acnGIS.Open
   
    sz = "select count(*) " _
        & "from vWater_Service_Areas " _
        & "where apn='" & strAPNShort & "' "
            
    Set rsinMisc = New ADODB.Recordset
    rsinMisc.Open sz, acnGIS, adOpenKeyset, adLockOptimistic
    If rsinMisc.EOF Then
:

Code Analysis:

  • Line 5 - hmm I shouldn’t concatinate variables into select statements. This opens a hole to allow injection attacks.
  • Line 11 - This block of code is used to access data from an oracle database. You need to have an oracle ODBC drivers installed on the computer running this code.
  • Line 32 - This block of code is used to access data from a SQL Server database.

Notice the main differences between Oracle, and SQL Server is the provider provided. And the difference between connecting to a sql server database, and an Access database (shown on line 3) is the Access database is ‘ConnectProject.Connection’