Solved Run-Time error '3051' (1 Viewer)

[Weekleyba]

I have a small database that is used by the users, only to get reports. No input.
The database is split and gets it's changing data from an Excel csv file.
I created a linked text file pointing to the Excel csv file. (I run another program to generate the Excel file)
Excel file is lengthy, with nearly 107,000 rows of data.
I had to use a text file due to one of the columns started with many numbers, but then changes to alphanumeric entries. Thus, since Access only looks at the first few entries (I think 16), it determines that field should be a Long Integer. In a text file, I can change it to Short Text.
That said, my problem is users will get the run-time error '3051' when someone else is in the database and has a report open, as shown below.
If no one else is in a report, all users are able to run a report, but only one at a time.
I think the problem is with linked texted file.
Any ideas on how to fix?

 

[bob fitz]

Perhaps you should have a split database with each user having their own copy of the front end, linked to a single backend. Import the data to the backend file. With a split db multiple users can use it at the same time

 

[Weekleyba]

Perhaps you should have a split database with each user having their own copy of the front end, linked to a single backend. Import the data to the backend file. With a split db multiple users can use it at the same time

It is split and each users has their own FE. But I think your on to something with importing the Excel file to the BE.
I'll try that and let you know.
Thanks!

 

[Uncle Gizmo]

since Access only looks at the first few entries (I think 16), it determines that field should be a Long Integer.

I had a similar problem many moons ago. The solution was to replace the first few rows of data with data in the format I wanted MS Access to import. You can add some dummy Rows to the beginning of the file, Rows filled completely with text. That will Force MS Access to import text

 

[The_Doc_Man]

I think the problem is with linked texted file.

You are correct. The linked Excel file uses Excel's whole-file locking whereas Access databases would use a more selective method of locking. Excel files do not share very well. You mentioned "changing data" i.e. the file is not constant. It gets updated. That means that the file is opened in WRITE mode, perhaps. I searched the forum and found an article on the problem. I'm not sure that you can ever actually do a SHARED WRITE operation on an Excel back-end file, but here is how you allow Excel backends to perform updates.

Access-Excel. How to edit an excel file from Access using SQL

As I'm sure you are aware, if you create a linked table to an excel sheet/range you can sort it, filter it, join it to other tables. What you can't do is edit the data. In many cases this does not matter since the requirement is to import the data or use as a reference. However on occasion...

www.access-programmers.co.uk

The REAL solution would be to convert the Excel file to be part of the back-end file in Access format. Then the question would be how often that dataset gets changed, because of potential bloat. However, 107,000 rows in Excel isn't that bad in Access. We have many users with million-row tables. I've gotten to over 600K records myself once or twice. So with proper indexing, it wouldn't be that bad.

 

[Weekleyba]

You are correct. The linked Excel file uses Excel's whole-file locking whereas Access databases would use a more selective method of locking. Excel files do not share very well. You mentioned "changing data" i.e. the file is not constant. It gets updated. That means that the file is opened in WRITE mode, perhaps. I searched the forum and found an article on the problem. I'm not sure that you can ever actually do a SHARED WRITE operation on an Excel back-end file, but here is how you allow Excel backends to perform updates.

Access-Excel. How to edit an excel file from Access using SQL

As I'm sure you are aware, if you create a linked table to an excel sheet/range you can sort it, filter it, join it to other tables. What you can't do is edit the data. In many cases this does not matter since the requirement is to import the data or use as a reference. However on occasion...

www.access-programmers.co.uk

The REAL solution would be to convert the Excel file to be part of the back-end file in Access format. Then the question would be how often that dataset gets changed, because of potential bloat. However, 107,000 rows in Excel isn't that bad in Access. We have many users with million-row tables. I've gotten to over 600K records myself once or twice. So with proper indexing, it wouldn't be that bad.

Thanks Doc, I will check this out.
I've tried creating the same text file in the BE but then I'm not able to link it to the FE. I guess you either can't link and linked table or you can't link to a text file since it's really not a table. ??

 

[The_Doc_Man]

If you tried to create that file in the BE via OLE embedding, I don't think you can use it as a table. If you could import that 107K rows as a normal Access table, it should be possible to use it normally. However, if the spreadsheet has more than 255 columns or if any row has more than about 4K bytes, it won't fit. Also, if any single cell has more than 255 bytes, that won't import correctly either.

 

[Pat Hartman]

To solve the bloat problem when I have to import data from external sources, I use a temporary BE. I create a BE with the table definitions and relationships I need. I link this db to my FE and load it with data to ensure that it works. Then I delete the data and compact the database and rename it myBE_template.accdb. To start the import process, my code copies the template to the local directory that holds the FE. In the process it renames it to myBE.accdb. That ensures that the links remain intact since the name of the BE and the table names stay constant. Then the process continues by linking to the spreadsheets (if the link isn't permanent) and use append queries to append to the tables in the linked temp BE.

We can help with code if this is the solution you think will work for you.

 

[gemma-the-husky]

I had a similar problem many moons ago. The solution was to replace the first few rows of data with data in the format I wanted MS Access to import. You can add some dummy Rows to the beginning of the file, Rows filled completely with text. That will Force MS Access to import text

Yes, I do that. Otherwise you can get a text column, but you can't stop access/excel deciding it's a numeric column. If you know how to select the dummy data, you can then delete it after the import. It's harder with excel. Probably easier with a CSV, as you can manipulate them more easily.

 

[Weekleyba]

Well, here's the solution I've come up that seems to work.
It involves a command button on the form that runs Docmd.TransferText with a saved specification that changes the column from numerical to short text. It first runs a delete query to clear the table that is to be updated.
Here's my form, highlighting the command button, table where data is stored and the delete query.

Here is my code for the Command Button shown above labeled, "Import - Allowance Status Data."

Private Sub cmdIMPORT_Click()
    Dim Msg1, Response1, Msg2, Response2, Style, Style2, Title1, Msg3, Title3, Style3, Response3, fName
    Msg1 = "Are you sure you want to IMPORT the Allowance Status Report?"
    Style1 = vbYesNo + vbCritical + vbDefaultButton2
    Title1 = "IMPORT"
    
    Msg2 = "Worksheet imported!"
    Style2 = vbOKOnly + vbInformation
    
    Msg3 = "Cannont find the file:" & vbCrLf & "'IHS - Allowance Status by Project and Location.csv'" & vbCrLf & " " & vbCrLf & "Please make sure that the CSV file is named correctly and in the correct location."
    Style3 = vbOKOnly + vbCritical
    Title3 = "ERROR - NO FILE FOUND"
        
    Response1 = MsgBox(Msg1, Style1, Title1)
    If Response1 = vbNo Then
        Exit Sub
    ElseIf Response1 = vbYes Then
    
        On Error GoTo SubError
        
        'This checks if the file exists.
        Const workFolder As String = "W:\DFM_Databases\IHS - Allowance Status by Project and Location.csv"
        If FileExists(workFolder) Then
           Else
              Response3 = MsgBox(Msg3, Style3, Title3)
           Exit Sub
        End If
        
        DoCmd.SetWarnings False
        
        ' This deletes the data in the table.
        DoCmd.OpenQuery "Delete_IHS - Allowance Status by Project and Location"
        
        fName = "W:\DFM_Databases\IHS - Allowance Status by Project and Location.csv"
        
        ' This runs the saved Import under the External Data tab where I changed the data type for the BAP column to short text.
        ' True means the spreadsheet has column names for the first row
        DoCmd.TransferText , acImportDelim, "IHS - Allowance Status by Project and Location", fName, True
              
            
        ' This provides the current date for display on the form.
        Response2 = MsgBox(Msg2, Style2, Title1)
        DoCmd.OpenQuery "UpdateImportTable_Q"
        DoCmd.RefreshRecord
      
        DoCmd.SetWarnings True
          
SubExit:
    On Error Resume Next
            Exit Sub
SubError:
        MsgBox "Error Number: " & Err.Number & " = " & Err.Description, vbCritical + vbOKOnly, _
           "An error occurred."
        GoTo SubExit
    
   End If
End Sub

NOTE: To create the specification, you need to create is under the External Data tab, then Text File.
There are a few steps in creating this spec file. An import one being, clicking the Advance button and then clicking Save As, and renaming the specification name to a shorter name than what we defaulted. The default name would not save. I'm assuming it is to long.

That's it!