Solved

Multiple step OLE DB error

Posted on 2004-09-08
13
403 Views
Last Modified: 2012-06-27
Hi Experts,
    I have a database that I have been using to compile work reports from 4 sources monthly. The reports come in as .mdb files named with a prefix for the user sending the report, the report name & month the report is for (example: prefix= "KN", report = "chk", month= "Aug" > Filename = "KNchkAug.mdb"). Then inside each file contains a set of tables having a standard set of names except that each tablename begins with the prefix for the user that sent it in like this, KN_Checklist.

    All was working fine until I decided it needed to be improved for timing sake. My front end has a form that I enter the current month and the user to import from and then I was using "DoCmd.TransferDatabase acLink" to update the link to the correct workreport BE, after which I would run append queries to compile the data as needed. Since upgrading to Access 2003 however the "TransferDatabase" method has been noticably slower than it used to be in previous versions (taking up to 3 seconds per linked table). So I decided to look for a better method and this is where my problem arrived. Here is the code I am trying to work with:

'********************************************
Function RefreshLink(newBE As String, newPrefix As String)

    Dim cat As Object
    Dim tbl As Object
    Dim oldNM As String
 
    Set cn = CreateObject("Adodb.Connection")
    Set cat = CreateObject("ADOX.Catalog")
    Set tbl = CreateObject("ADOX.Table")
 
    cat.ActiveConnection = CurrentProject.Connection 'cn
 
    For Each tbl In cat.Tables
        If tbl.Type = "LINK" Then
            'mytbprop = tbl.Properties
            oldNM = tbl.Properties("Jet OLEDB:Remote Table Name")
            oldNM = Right(oldNM, Len(oldNM) - 2)
            With tbl
                .Properties("Jet OLEDB:Remote Table Name") = newPrefix & oldNM
                .Properties("Jet OLEDB:Link Datasource") = newBE
            End With
        End If
    Next
 
Set cat = Nothing
Set tbl = Nothing
 
End Function
'********************************************

when the function hits ".Properties("Jet OLEDB:Remote Table Name") = newPrefix & oldNM" I get the following error:

        Run-time error '2147217887 (80040e21)':
        Multiple step OLE DB opperation generated errors. Check each OLE DB
        status value, if available. No work was done


Any help? I have little time to work on this today, so maximum points.

TIA!

Sam
0
Comment
Question by:will_scarlet7
  • 7
  • 5
13 Comments
 
LVL 41

Assisted Solution

by:shanesuebsahakarn
shanesuebsahakarn earned 250 total points
ID: 12005463
How about just running an append query without linking to the tables?

INSERT INTO SomeTable SELECT * FROM AnotherTable IN 'C:\SomeOtherDB.mdb'
0
 
LVL 15

Author Comment

by:will_scarlet7
ID: 12005546
ThanX for the response shane! I will consider your suggestion, but I am hoping to get the linking resolved. One reason for which is that at current I have the process broken up into steps, so that after linking the data I can preview it before appending it if needed. I know I can work around this by using ADO recordsets, but the link seems like the simplest.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12005608
Does it work if you swap around the Link Datasource and Remote Table Name lines? I'm not terribly familiar with ADOX.
0
 
LVL 15

Author Comment

by:will_scarlet7
ID: 12005923
It causes the same error.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12005950
:( I could tell you how to do with DAO, but I'm not sure if that would be any faster.
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 12006881
This is one of those "catchall" errors that ADO throws at times ... are you sure you're supplying enough information to the complete the operation? ADOX is pretty fickle about the order of items, and if you try to "set" an object or property without something else already being set, it'll choke on you. Check this link to make sure you're issuing the correct items in the correct order:

http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q240/2/22.ASP&NoWebContent=1
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 15

Author Comment

by:will_scarlet7
ID: 12007230
Thank you both, Sane & LSM!

    I think the problem lies in that the ".Properties("Jet OLEDB:Remote Table Name")" must be a read only property once the link is made. So nice of our dear Microsoft to just give generalities in their error message...

    What I opted for was a change in tactic. The code below moves through a recordset to get the old link name and delete it then import a new link to the current BE.

Sub CreateLinkedTableADO(dbBE As String)
   
    Dim cat As Object
    Dim rs As Object
    Dim tbl As Object
    Dim strSQL As String
 
    Set cn = CreateObject("Adodb.Connection")
    Set rs = CreateObject("Adodb.Recordset")
    Set cat = CreateObject("ADOX.Catalog")
    Set tbl = CreateObject("ADOX.Table")
 
    cat.ActiveConnection = CurrentProject.Connection
    strSQL = "SELECT * FROM stpLinkedTables WHERE stpLinkedTables.Category = 'ABM';"
    rs.Open strSQL, CurrentProject.Connection, 2, 1
    rs.MoveFirst
   
    While Not rs.EOF
        DoCmd.DeleteObject acTable, rs("LinkName")
        Set tbl = New ADOX.Table
        Set tbl.ParentCatalog = cat
        With tbl
            .Name = rs("LinkName")
            .Properties("Jet OLEDB:Create Link") = True
            .Properties("Jet OLEDB:Remote Table Name") = rs("TableName")
            .Properties("Jet OLEDB:Link Datasource") = dbBE
        End With
        cat.Tables.Append tbl
        rs.MoveNext
    Wend
   
    rs.Close
    Set rs = Nothing
    Set cat = Nothing
    Set tbl = Nothing

End Sub

Seems to be working good so far (and ten times faster than the TransferDatabase method).

All your help was appreciated. Are you OK if I split the points between you with a grade of "B"? I'd rather do that then have the question PAQ'd since I don't need the points refunded. Let me know your preference.

God bless!

Sam
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12007251
> Sane & LSM!

Sane I am not :) It's ok by me, Sam but I'll leave it up to you.
0
 
LVL 15

Author Comment

by:will_scarlet7
ID: 12007281
Sorry Shane...
0
 
LVL 15

Author Comment

by:will_scarlet7
ID: 12007372
Any chance I can post a second question about the above code (not @ ADO) here and you answer that way I can award an A for the answer? Or should I close this one and open a new one?
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12007391
I don't mind either way Sam, so long as I can help :)
0
 
LVL 15

Author Comment

by:will_scarlet7
ID: 12007513
I'll close this with a "B" and open a new one (more points available to be earned that way)

ThanX again!

Sam
0
 
LVL 15

Author Comment

by:will_scarlet7
ID: 12007569
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Text Value from Time 3 23
Splitting out Data 14 27
Access MDB/PDF 21 31
How to calcualate lateness in Access 2010 11 25
This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now