Run-time error 2507 (1 Viewer)

Kevin320 · Jan 30, 2015

Hi all,

I have a situation that has me puzzled, and I'd like to see if anyone has any suggestions.

I have an Access database that runs daily, and periodically needs to archive certain tables. I'm trying to write the vba code to do so, but I'm getting Run-time error 2507 - "The type isn't an installed database type or doesn't support the operation you chose"

My code is below. I'm using DoCmd.TransferDatabase, and that is the command that is giving the error. I've tried acTable (as shown in the code below), acDefault, and leaving it blank. Is there a reference that's needed for this that may be lacking? Both the source and destination databases have Microsoft Access 14.0 Object Library referenced. I appreciate any suggestions.

Code:

    Dim strDay, strMonth, strYear As String
    Dim strRPT141DatabaseSourcePath, strRPT141DatabaseDestinationPath As String
    Dim strRPT141FolderPath As String
    Dim strRPT141TableName As String
 
    If ggStrPreviousBusinessDate = "" Then
        Call GetPreviousBusinessDate
    End If
 
    strDay = Right("0" & Day(ggStrPreviousBusinessDate), 2)
    strMonth = Right("0" & Month(ggStrPreviousBusinessDate), 2)
    strYear = Year(ggStrPreviousBusinessDate)
 
 
    'Check to see if the current month's RPT141 folder exists
    strRPT141FolderPath = "[URL="file://\\coltrane\Margins\Daily"]Z:\Margins\Daily[/URL] Runs\RPT141_" & strYear & "-" & strMonth
 
    If Len(Dir(strRPT141FolderPath, vbDirectory)) = 0 Then
      MkDir strRPT141FolderPath
    End If
 
    'Check to see if the current month's RPT141 Access Database archive exists
    strRPT141DatabaseDestinationPath = strRPT141FolderPath & "\RPT141_" & strYear & "-" & strMonth & "-" & strDay & ".accdb"
 
    If Len(Dir(strRPT141DatabaseDestinationPath)) = 0 Then
        strRPT141DatabaseSourcePath = "Z:[URL="file://\\Margins\Daily"]\Margins\Daily[/URL] Runs\RPT141-Template.accdb"
        FileCopy strRPT141DatabaseSourcePath, strRPT141DatabaseDestinationPath
    End If
 
    'Export the current RPT141 tables into the current month's RPT141 Access Database archive
    strRPT141TableName = "tblRPT093_" & strYear & strMonth & strDay
 
    DoCmd.TransferDatabase acExport, , strRPT141DatabaseDestinationPath, acTable, "tblRPT093", strRPT141TableName

CJ_London · Jan 31, 2015

have you stepped through the code and checked directories and files exist as expected before running the transferdatabase?

GinaWhipp · Jan 31, 2015

Hmm, in addition to what CJ_London said...

I would use UNC paths, less confusion for Access should the file be moved to machine where the User did not map it to *Z*. Also, what line highlights when it errors. If none you can put MsgBox after each part to determine what it's getting hung up on.

Kevin320 · Feb 2, 2015

CJ_London said:
have you stepped through the code and checked directories and files exist as expected before running the transferdatabase?

Hi CJ, yes, I've stepped through the code, and everything works great until the transferdatabase command. All directories and files exist, or are created if they don't exist.

Kevin320 · Feb 2, 2015

GinaWhipp said:
Hmm, in addition to what CJ_London said...

I would use UNC paths, less confusion for Access should the file be moved to machine where the User did not map it to *Z*. Also, what line highlights when it errors. If none you can put MsgBox after each part to determine what it's getting hung up on.

Hi Gina,

I actually use UNC paths instead of the drive letter in my code, but changed the text in the code provided in this thread for simplicity (and because my company doesn't want internal server names/paths disclosed).

The line that highlights when it errors is the DoCmd.TransferDatabase line.

CJ_London · Feb 2, 2015

I've done a quick search on error 2507 and came across this thread

http://www.pcreview.co.uk/forums/run-time-error-2507-a-t3780386.html

Access is supposed to be the default but perhaps worth a try?

Kevin320 · Feb 2, 2015

CJ_London said:
I've done a quick search on error 2507 and came across this thread

http://www.pcreview.co.uk/forums/run-time-error-2507-a-t3780386.html

Access is supposed to be the default but perhaps worth a try?

Wow! Something so simple, and that was the answer. I did a search on the error, but didn't come across this one. Even though Microsoft says "Microsoft Access" is the default, it evidently isn't.

I simply changed the DoCmd line to the following:

Code:

DoCmd.TransferDatabase acExport, "Microsoft Access", strRPT141DatabaseDestinationPath, acTable, "tblRPT093", strRPT141TableName

Thank you very much CJ

Run-time error 2507 (1 Viewer)

Kevin320

Registered User.

CJ_London

Super Moderator

GinaWhipp

AWF VIP

Kevin320

Registered User.

Kevin320

Registered User.

CJ_London

Super Moderator

Kevin320

Registered User.

Similar threads

Users who are viewing this thread