Hi!

I have inherited a database that was made in Access 2003 and I've updated it to 2007 and now I'm trying to use it on 2010. There is one code that just won't run and I think it maybe a problem with the references. I didn't write this code and I have no idea how to even start trouble shooting. When I run it, I get a compile error and it says that "User defined type not defined". It highlights 'Private Sub Command0_Click()" in yellow and this part in blue: "Dim MyConn As ADODB.Connection".

Any help would be appreciated and thanks in advance.

Here's the problem area:

Private Sub Command0_Click()

Dim Ignored_variable As Boolean
Ignored_variable = IfTableExistDelete(GetFullPath(), "T1")

Dim sSQLQuery As String

sSQLQuery = "SELECT F1.date, F1.scan_time, MIN(F1.chimp_id) as Chimp_Ids, MIN(F1.chimp_id) as Min_Chimp_Id, F1.observer_id " & _
"into T1 " & _
"FROM PC_temp1 as F1 " & _
"GROUP BY F1.date, F1.observer_id, F1.scan_time ;"

Dim MyConn As ADODB.Connection
Dim MyRecSet As ADODB.Recordset
Set MyConn = New ADODB.Connection
MyConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & GetFullPath() & ";"

MyConn.Open
'Execute SQL query
MyConn.Execute (sSQLQuery)
MyConn.Close

sSQLQuery = "Update T1, PC_temp1 " & _
"Set T1.Chimp_Ids = T1.Chimp_Ids + ' ' + PC_temp1.chimp_id, T1.Min_Chimp_Id = PC_temp1.chimp_id " & _
"WHERE PC_temp1.date = T1.Date and PC_temp1.observer_id = T1.observer_id and PC_temp1.scan_time = T1.scan_time and " & _
"T1.Min_Chimp_Id <> PC_temp1.chimp_id ;"

MyConn.Open
'Execute SQL query
MyConn.Execute (sSQLQuery)
MyConn.Close

Ignored_variable = IfTableExistDelete(GetFullPath(), "PC_Output")

MyConn.Open
sSQLQuery = "SELECT * into PC_Output from T1 Where 0 = 1;"
MyConn.Execute (sSQLQuery)

sSQLQuery = ""
sSQLQuery = "SELECT * FROM T1 ; "
Set MyRecSet = MyConn.Execute(sSQLQuery)
Dim sSortedChimpIds As String
Dim aStrArray

If MyRecSet.BOF Then
' No data has been returned .. no need to add the items to the
' list view.
MsgBox " No records returned"
Else
'Records present.. setting up list of items
Dim sChimpsList As String
Do Until MyRecSet.EOF
sChimpsList = Nz(Trim(MyRecSet(2)), "")
aStrArray = Split(sChimpsList, " ")
aStrArray = SortArray(aStrArray)
sSortedChimpIds = Join(aStrArray)
sSQLQuery = ""
sSQLQuery = "Insert into PC_Output ([date],[scan_time],[Chimp_Ids],[Min_Chimp_Id],[observer_id])" & _
" VALUES ( '" & MyRecSet(0) & "','" & MyRecSet(1) & "','" & MyRecSet(2) & "','" & sSortedChimpIds & "','" & MyRecSet(4) & "'); "

MyConn.Execute (sSQLQuery)
MyRecSet.MoveNext
Loop
End If

MyConn.Close
Ignored_variable = IfTableExistDelete(GetFullPath(), "T1")

End Sub

This is probably a References issue. Did you look to see if any references are listed as missing?

It's unlikely it'll be listed as missing.
You'll need to explicitly add a reference to
Microsoft ActiveX Data Objects 2.x Library (where x can be from 1 to 8).