Advertisement

10.12.2008 at 03:00PM PDT, ID: 23808118 | Points: 500
[x]
Attachment Details

type mismatch errror access database

Asked by duponttc24 in Access Forms, SQL Query Syntax, Editors IDEs

Tags:

I am using the following code to try and clean up the odbc connection in all of the queries and I keep getting a typ mismatch error can you help me.  Thank you.

Start Free Trial 
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:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:
186:
187:
188:
189:
190:
191:
192:
193:
194:
195:
196:
197:
198:
199:
200:
201:
202:
203:
204:
205:
206:
207:
208:
209:
210:
211:
212:
213:
214:
215:
216:
217:
218:
219:
220:
221:
222:
223:
224:
225:
226:
227:
228:
229:
230:
231:
232:
233:
234:
235:
236:
237:
238:
239:
240:
241:
242:
243:
244:
245:
246:
247:
248:
249:
250:

Option Compare Database
 
Public Sub btncancel_click()
    ' When press cancel button end program.
    End
End Sub
 
Public Sub btnUpdate_Click()
    ' Create Variable for results
    Dim result As VbMsgBoxResult
    
    
  
   
    Dim Password1 As String
    Dim Password2 As String
    
    
    
    ' Make txtbox passwords equal to the pasword variables
    Password1 = txtPword1
    Password2 = txtPword2
    
    If Password1 <> Password2 Then
         MsgBox "Password 1 and Password 2 do no match.  Please " + _
                "correct the password before moving on.", _
                vbOKOnly Or vbInformation, _
                "Informix Pasword Updater."
        
    Else
    ' Once press update button display msg box that makes user confirm they
    ' they want to update the passwrd.
    result = MsgBox("Are you sure you want to update informix password?", _
    vbYesNo Or vbExclamation, _
    "Informix Password Updater")
    
    ' If presses yes then updates the databases.
    If result = vbYes Then
        EditQueryProp (test)
            
    ' If chooses no then displays message box showing that pass
    Else: MsgBox "Did not update informix password.", _
            vbOKOnly Or vbInformation, _
            "Informix Password Updater"
        End If
        
    End If
End Sub
 
' If not successful displays message box that lets the user know.
Public Function EditQueryProp(strQueryName As String)
 
' Create
On Error GoTo Err_EditQueryProp
    ' Create variables for the databases, the query and the ouput.
    Dim db As DAO.database
    Dim db2 As DAO.database
    Dim qdf As DAO.QueryDef
    Dim output As String
    Dim rec As DAO.Recordset
    Dim defaultODBC As String
    Dim qdfLoop As QueryDef
    
    Set db2 = CurrentDb
    Set rec = db2.OpenRecordset("Select * from Databases;")
    defaultODBC = "='ODBC;DSN=HDW;DB=ais;HOST=sacpsr4002;SERV=54322;SRVR=aispp_port;PRO=onsoctcp;OAC=1;DDFP=1;DNL=1;'"
    output = txtPword1
    
    While Not rec.EOF
    Set db = DAO.DBEngine.OpenDatabase("H:\NMS Coll Telephony\Databases\" & rec(1))
    Set qdf = db.QueryDefs("get_informix_password")
    
  
    ' Connect to the database and update the password information.
    qdf.Connect = "ODBC;DSN=HDW;DB=ais;HOST=sacpsr4002;SERV=54322" + _
                    ";SRVR=aispp_port;PRO=onsoctcp;OAC=1;DDFP=1;DNL=1;UID=taylorad;PWD=" + _
                    output + ";"
    
    
       
    With db
    
        'Debug.Print .QueryDefs.Count & _
             " QueryDefs in " & .name
        ' Enumerate TableDefs collection.
        For Each qdfLoop In .QueryDefs
            
            If ((InStr(1, qdfLoop.Name, "get_", 1) = 0) And (Len(qdfLoop.Connect) > 0)) Then
                With qdfLoop
                    If InStr(1, qdfLoop.Connect, "ais", 1) > 0 Then
                        'Debug.Print defaultODBC
                        .Connect = defaultODBC
                        
                    End If
                    'Debug.Print "Name:        " & .Name
                    'Debug.Print "Old Connect: " & .Connect
                    strNewConnect = funcOdbcConnectRemoverUserAndPasword(.Connect)
                    If strNewConnect = "ODBC" Then strNewConnect = "ODBC;"
                    .Connect = strNewConnect
                    Debug.Print "New Connect: " & .Connect
                End With
            End If
        Next qdfLoop
    End With
                                     
                    
    Debug.Print qdf.Properties(6)
    rec.MoveNext
    
    Wend
    
    ' Move to the next database
    ' Make database equal to next field.
    
    Set db = DAO.DBEngine.OpenDatabase("H:\Skip Trace\Western Union\WU_File_Process.mdb")
    Set qdf = db.QueryDefs("get_informix_password")
    
    
    ' Connect to the database and update the password information.
    qdf.Connect = "ODBC;DSN=HDW;DB=ais;HOST=sacpsr4002;SERV=54322" + _
                    ";SRVR=aispp_port;PRO=onsoctcp;OAC=1;DDFP=1;DNL=1;UID=taylorad;PWD=" + _
                    output + ";"
                    
    Debug.Print qdf.Properties(6)
    ' Move to the next database
    ' Make database equal to next field.
    
    Set db = DAO.DBEngine.OpenDatabase("K:\HomEq Coll Telephony\Customer Service Reporting\RT_Graphs\DB\RealTime_Graphs.mdb")
    Set qdf = db.QueryDefs("get_informix_password")
    
    
    ' Connect to the database and update the password information.
    qdf.Connect = "ODBC;DSN=HDW;DB=ais;HOST=sacpsr4002;SERV=54322" + _
                    ";SRVR=aispp_port;PRO=onsoctcp;OAC=1;DDFP=1;DNL=1;UID=taylorad;PWD=" + _
                    output + ";"
                    
    Debug.Print qdf.Properties(6)
    ' Move to the next database
    ' Make database equal to next field.
    
    Set db = DAO.DBEngine.OpenDatabase("H:\NMS Coll Telephony\Databases\Loan_Assignment_mods.mdb")
    Set qdf = db.QueryDefs("aaaget_informix_password")
    
    
    ' Connect to the database and update the password information.
    'qdf.Connect = "ODBC;DSN=HDW;DB=ais;HOST=sacpsr4002;SERV=54322" + _
                    ";SRVR=aispp_port;PRO=onsoctcp;OAC=1;DDFP=1;DNL=1;UID=taylorad;PWD=" + _
                    output + ";"
                    
    Debug.Print qdf.Properties(6)
    ' Move to the next database
    ' Make database equal to next field.
    
    ' If successful diplay message box that lets the user know.
    MsgBox "Update Complete.", _
            vbOKOnly Or vbInformation, _
            "Informix Password Updater"
 
' Exit the query editor.
Exit_EditQueryProp:
    Set qdf = Nothing
    Set db = Nothing
    Exit Function
 
Err_EditQueryProp:
    MsgBox Err.Number & ", " & Err.Description, , "Error", _
    vbOKOnly Or vbInformation, _
    "Informix Password Updater"
    Resume Exit_EditQueryProp
    
    
    ' If successful diplay message box that lets the user know.
    
    
    
End Function
Function xg_GetSubString(mainstr As String, n As Integer, delimiter As String) As String
'* Get the "n"-th substring from "mainstr" where strings are delimited by "delimiter"
    Dim i As Integer
    Dim substringcount As Integer
    Dim pos As Integer
    Dim strx As String
    Dim val1 As Integer
    Dim w As String
    
 
On Error GoTo Err_xg_GetSubString
 
w = ""
substringcount = 0
i = 1
pos = InStr(i, mainstr, delimiter)
Do While pos <> 0
    strx = Mid(mainstr, i, pos - i)
    substringcount = substringcount + 1
    If substringcount = n Then
        Exit Do
    End If
    i = pos + 1
    pos = InStr(i, mainstr, delimiter)
Loop
 
If substringcount = n Then
    xg_GetSubString = strx
Else
    strx = Mid(mainstr, i, Len(mainstr) + 1 - i)
    substringcount = substringcount + 1
    If substringcount = n Then
        xg_GetSubString = strx
    Else
        xg_GetSubString = ""
    End If
End If
 
Exit Function
 
Err_xg_GetSubString:
   Debug.Print Error$, True
    Resume Next
 
End Function
 
Function funcOdbcConnectRemoverUserAndPasword(strConnect As String) As String
    Dim strWord, strBuild As String
    Dim intI    As Integer
     
 
    funcOdbcConnectRemoverUserAndPasword = strConnect
 
On Error GoTo err_funcOdbcConnectRemoverUserAndPasword
 
    If Len(strConnect) > 0 Then
      intI = 1
      strBuild = ""
      Do
        strWord = xg_GetSubString(strConnect, intI, ";")
        If (InStr(1, strWord, "UID=", 1) = 0 And InStr(1, strWord, "PWD=", 1) = 0 And strWord <> "") Then
                strBuild = strBuild & strWord & ";"
        End If
        intI = intI + 1
      Loop Until strWord = ""
      'Debug.Print strConnect
      'Debug.Print strBuild
      funcOdbcConnectRemoverUserAndPasword = strBuild
    End If
Exit Function
 
err_funcOdbcConnectRemoverUserAndPasword:
Debug.Print Error$, True
End Function
[+][-]10.12.2008 at 03:06PM PDT, ID: 22699069

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]10.12.2008 at 03:08PM PDT, ID: 22699077

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]10.12.2008 at 03:15PM PDT, ID: 22699097

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
 
Loading Advertisement...
20080716-EE-VQP-32 - Hierarchy / EE_QW_2_20070628