Works in 2003, throws error: 2507 in 2007

[SiobhanP]

I have a complicated series of functions whose purpose is to produce a database file that sits in a folder on our network until a completely separate process in another database sucks it in for reporting purposes.

These functions work perfectly in Access 2003. We've begun rolling out Access 2007 to folks and this process has stopped working for people with that version. I can't figure out what, exactly, is wrong, or how to fix it.

I've attached a screen snap of the error that it produces when it's being run in Access 2007.

Here's the function that sets the database type:

Public Function EnbUpdate() As Boolean
  ' Comments  : creates the "se-perm" Paradox table for the ENB
  ' Parameters: None
  ' Returns   : True if ENB update successful, False otherwise
  '
  On Error GoTo PROC_ERR

  ' type of table to export
  Const csDatabaseType As String = "Paradox 4.X"

  ' table to export
  Const csTable As String = "se-perm"
  
  Dim cmd As ADODB.Command
  Dim strMsg As String
  Dim blnIsEmpty As Boolean
  Dim intReply As Integer
  Dim strSQL As String
    
  Set cmd = New ADODB.Command
  
  ' get confirmation
  intReply = MsgBox("Continue with ENB Update?", vbYesNo + vbQuestion, mcsENBTitle)
  If intReply = vbNo Then
    EnbUpdate = False
    Exit Function
  End If

  ' empty the ENB table
  Call EmptyTable(csTable)

  ' get all eligible projects and append to the ENB table
  With cmd
    .ActiveConnection = CurrentProject.Connection
    .CommandText = "qryAppendENB"
    .CommandType = adCmdStoredProc
    .Execute
  End With

  ' get rid of the previous external table, if it exists
  On Error Resume Next
  Kill mcsDatabase & csTable & "db"
  
  ' transfer the new table
  On Error GoTo TRANSFER_ERR
  DoCmd.TransferDatabase acExport, csDatabaseType, mcsDatabase, acTable, csTable, csTable
  On Error GoTo PROC_ERR
  
  ' get projects with blank DEC IDs
  blnIsEmpty = IsViewEmpty("qryENBUpdateBlankDecID")
  If blnIsEmpty Then
    MsgBox "ENB Update Finished"
  Else
    DoCmd.OpenQuery "qryENBUpdateBlankDecId"
    MsgBox "These projects have a blank DEC ID"
  End If
  
  Set cmd = Nothing
  
  EnbUpdate = True
  
PROC_EXIT:
  Exit Function
PROC_ERR:
  EnbUpdate = False
  Resume PROC_EXIT
TRANSFER_ERR:
  MsgBox "Error number " & Err.Number & ": " & Err.Description & vbCrLf _
  & "ENB Tables were not transferred", vbCritical, mcsENBTitle
  EnbUpdate = False
  Resume PROC_EXIT
End Function

It clearly calls a bunch of other code to build the data for transfer. I can't figure out why it's claiming that database type isn't OK when it's in the list of valid types. This code has been working just fine for at least ten years. Nothing has been changed in this particular database at all. The only change has been using Access 2007. I reiterate, when I run this process using Access 2003, it works fine.

Anyone have any ideas either how to fix this, or where to find a solution?

 

[boblarson]

That's strange for Access 2007. For 2010 it would be expected because, as you can see here:
http://blogs.office.com/b/microsoft...-2010-deprecated-features-and-components.aspx

Paradox (3, 4, 5, 6, 7) ISAM, Lotus 1-2-3 ISAM and Red 2 ISAM or Jet 2

Export, import and linking to data from Paradox 3, 4, 5, 6, 7, Lotus 1-2-3, Access 1.0 and 2.0 (Red 2, or Jet 2) will not be available in Access 2010. User may see the following error: Installable ISAM not found.

You will need Access 2007 or older in order to be able to export, import or link data from Paradox 3, 4, 5, 6, 7, Lotus 1-2-3, Access 1.0 and 2.0 (Red 2, or Jet 2) files.

But it should work in Access 2007. What version of Windows are you on? Did that change at all too?

 

[SiobhanP]

That's strange for Access 2007...But it should work in Access 2007. What version of Windows are you on? Did that change at all too?

Ok, did some checking. Apparently they just started rolling out computers with Office 2010 on them. It's 2010 this isn't working on. *sigh* Now I have THREE versions of Access I'm trying to support. Anyway, thanks. Now we're looking into rewriting this entire process and automating it in SQL because it's going to break a whole bunch of databases as more 2010 is rolled out.

Thanks so much for figuring this out. It was driving me quite mad.