MyDB - User Defined Type Not Defined (but I have??)

[londonkiwi]

I have produced an Import_Data() Function. It aim is to import processed data (a txt file), (checking validity duplicates etc) and place that data in the appropriate tables.

I have the following defined "Dim MyDB As DATABASE"

I have received the following error msg, and cannot understand why it has arisen

"Complie Error - User-defined type not defined"

I apologise in advance, but have pasted the code below. Can someone please tell me what dumb
things I have done, and how to fix it...please

Function Import_Data()

Dim file_name As String
Dim SITE_NO As String
Dim SURVEY_NO As String
Dim SITE_NAME As String
Dim No_Records As Integer
Dim WADT As Integer
Dim AADT As Integer
Dim GROUP As String

'Dim Criteria As String, MySet As Recordset
Dim Criteria As String
Dim MyDB As DATABASE
Dim MySet As Recordset

On Error GoTo MyError:


' Get name of file to be imported into the database, call the Function GetFileName
file_name = GetFileName()
file_name = Trim(file_name)

' Cancel press on Dialog Box
If file_name = "" Then
Exit Function
End If

' Display the HourGlass on the screen
DoCmd.Hourglass True

' Delete old records in TC_IMPORT
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * From TC_IMPORT"
DoCmd.SetWarnings True

' Import text file to a temp table before appending it

DoCmd.TransferText acImportDelim, "IMPORT_SPEC", "TC_IMPORT", file_name

' Get site number and survey number from temp table
SITE_NO = DFirst("[SITE_NO]", "TC_IMPORT", "&quot
SURVEY_NO = DFirst("[SURVEY_NO]", "TC_IMPORT", "&quot


' Count the number of records in the TC_IMPORT table
No_Records = DCount("*", "TC_IMPORT&quot


'Check to see if the Site_No is in TC_INDEX table

Criteria = "SITE_NO = '" & SITE_NO & "'" ' Define search criteria.
Set MyDB = DBEngine.Workspaces(0).Databases(0)

' WAS Set MySet = MyDB.OpenRecordset("TC_INDEX", DB_OPEN_DYNASET) ' Create dynaset.

Set MySet = MyDB.OpenRecordset("TC_INDEX", dbOpenDynaset) ' Create dynaset.


MySet.FindFirst Criteria ' Locate first occurrence.

If MySet.NoMatch Then
DoCmd.Hourglass False
MsgBox "The Site Number for this file is not in the Database. Enter the site details in table TC_INDEX and try again", 16, "ERROR"
'Quit Sub
Exit Function
Else
GROUP = MySet![GROUP]
End If

MySet.Close ' Close table.


'Check to see if Temp_table has already been appended to the table TC_3C3S. These two fields can
' be considered the primary key. Consider future counts on future dates!

Criteria = "SITE_NO = '" & SITE_NO & "' AND SURVEY_NO = '" & SURVEY_NO & "'" ' Define search criteria.



' WAS Set MySet = MyDB.OpenRecordset("TC_3C3S", DB_OPEN_DYNASET) ' Create dynaset.
Set MySet = MyDB.OpenRecordset("TC_3C3S", dbOpenDynaset) ' Create dynaset.


MySet.FindFirst Criteria ' Locate first occurrence.

If Not MySet.NoMatch Then
DoCmd.Hourglass False
MsgBox "The data has already been imported. Can not import over existing data", 16, "All Ready in Database"
'Quit Sub
Exit Function
Else ' Temp_Table not all ready in Database
DoCmd.SetWarnings False
' Run query to append TC_IMPORT to TC_3C3s table
DoCmd.RunSQL Append_Query()


' Run query to add record to TC_Summary table

'Calaculate WADT for both direction
WADT = Cal_WADT(No_Records)

' Calculate AADT for section of road using traffic count guideline (Nov 94)
AADT = Cal_AADT(SURVEY_NO, WADT, GROUP)

' Add record for lane 1 to TC_Summary , Only half records will be used eg lane 1
DoCmd.RunSQL Summary_Query(CStr(No_Records / 2), 1, WADT, AADT)

' Add record for lane 2 to TC_Summary , Only half records will be used eg lane 2
DoCmd.RunSQL Summary_Query(CStr(No_Records / 2), 2, WADT, AADT)

DoCmd.SetWarnings True
End If

MySet.Close ' Close table.
MyDB.Close ' Close data base

' Turn off Hourglass
DoCmd.Hourglass False
MsgBox "File Import Correctly", 0, "OK"

' Ever thing went Ok EXIT sub before doing error handling stuff
Exit Function

' Error handling routine
MyError:

' Display Error message box and resumes
MsgBox Error(Err)
DoCmd.Hourglass False
MsgBox "Houston - we have a problem - Error Importing File. Not Done Correctly", 16, "ERROR"
Exit Function

End Function

 

[raskew]

It certainly sounds like a missing reference. With a module open, click on Tools/References and look for references marked Missing. If found, clear the check box, then shut down/restart your application, once again examining your references. If that doesn't clear the problem, please post back.

 

[londonkiwi]

Ok, thanks for answering

went to Tool/References. The following were "ticked"

Visual basic for Applications, Micorsoft Access 9.0 Object Library, OLE Automation, Microsoft AxtiveX Data Objects 2.1 Library, Microsoft Common DIalog Control 6.0

Nothing was marked "missing"

Do I need to load a Reference?

I can send you the full dbase (if you've got time to look!)

 

[raskew]

I'm assuming this is Access 2000. If so, include the Microsoft DAO 3.6 Object Library. Think you'll find that's the problem.

 

[londonkiwi]

Yes - seems to fix that one.

No I get an error msg "Complie Error - Method or Data Member Not Found"

This appears at or above the line
MySet.FindFirst Criteria ' Locate the first occurence.

Can you please help further??

thanks very much

 

[raskew]

I'm not sure if this is a factor, but both 'Criteria' and 'Group'--names you assigned to objects, are Reserved Words and have special meaning. Prior to my previous response, I tested Dimensioning both as strings, and didn't encounter any problems. However, renaming them is something you might try.

If this doesn't work, email a copy of your application and I'll take a look at it.

Bob
raskew@centurytel.net

 

[londonkiwi]

OK - I've been advised to install ADO 2.6 (part of MDAC).

I got the download, ran it and installed it. I cannot STILL cannot find Microsoft ActiveX Data Objects 2.60 (Tools/References) insdie the module of the Access2000 dbase I'm running.

PLEASE TELL WHAT I"M doing wrong??

 

[JeffCharvat]

We were having this same problem today. I followed the advice to mark the MS DAO 3.6 object library and the problem moved similarly to yours. Ours went to a recordset.edit call.

So I marked "MS ActiveX Data Objects 2.5 Library" and now everything is compiling.

Hope this works for you too.

Jeff

 

[andylec]

You've declared a recordset, but not declared which type, so it is using ADO as that is the first Reference in the list. Declare it as DAO.Recordset or ADODB.Recordset, depending on how you want to use it.

 

[londonkiwi]

Thanks for the followup's.