Start Free Trial

asked on

Mutiple access to a linked Test table

I have a text file that is auto gerenrated from a system, and I link this text file to one of my Access database as table. Everything works well, but now I have mutiple access databases will also need to access this text file table as the same time since I use this linked table as data source of combo box. I got a message "The Microsoft Jet database engine cannot open the file "txttable". It is already opened exclusively by another user, or you need permission to view its data."

So what other options that I have if I need to open this linked text table in mutiple acess database at a same time?

Thanks in advance.

Import the data into your Access file instead of linking to it.

ASKER

But the data is changed daily. If I import the data instead of directly liink it, I will need to import it everyday. I am look for a solution to avoid extra steps.

Import the data manually to a new table once, then write code to automatically import the data from the text file every time the database is opened. You will first have to delete the data in the table before importing it. The two TransferText code samles are for delimited and fixed width text files.

    Dim cnn As ADODB.Connection, strSQL As String
    Set cnn = CurrentProject.Connection
    strSQL = "DELETE * FROM MyTable"
    cnn.Execute strSQL
 
    DoCmd.TransferText acImportDelim, "SpecificationName", "TableName", "FilePathAndName", True
    DoCmd.TransferText acImportFixed, "SpecificationName", "TableName", "FilePathAndName", True

Open in new window

ASKER

Thanks. Where should I place those codes?

One possibility is to place the code in the Form_Load event on the first form the user sees.

ASKER

But user will open the form more than 10 times a day. How can I limit to run the codes only first time of loadding form per day?

Thanks.

Simple solution could be to create a dummy table with a date column/field. When the form is opened have it query that dummy table first to see if today's date is present, if not run your code and then add (insert) todays date to that dummy table.

ASKER

Can you provide more details of how to "have it query that dummy table first to see if today's date is present, if not run your code and then add (insert) todays date to that dummy table?"

Thanks

1. Add a date field to your table after the first import.
2. Before import, check the date field.
3. If the date is not today,
4. Clear the table and import.
5. Update the date field after import.

    Dim cnn As ADODB.Connection, strSQL As String
    Set cnn = CurrentProject.Connection
    Dim rst As New ADODB.Recordset, blnUpdate As Boolean
    ' get the date the data was uploaded
    strSQL = "SELECT DateUploaded FROM MyTable"
    With rst
        .Open strSQL, cnn, adOpenKeyset, adLockReadOnly
        ' if the data is not today,
        If Fields("DateUploaded") <> Format(Now, "Short Date") Then
            ' set the update flag to true
            blnUpdate = True
        End If
    End With
    ' if the update flag is true,
    If blnUpdate Then
        ' delete the data from the table
        strSQL = "DELETE * FROM MyTable"
        cnn.Execute strSQL
        ' import the data from the text file using either delimited or fixed width
        DoCmd.TransferText acImportDelim, "SpecificationName", "TableName", "FilePathAndName", True
        ' OR
        DoCmd.TransferText acImportFixed, "SpecificationName", "TableName", "FilePathAndName", True
        ' set the date uploaded to today
        strSQL = "UPDATE MyTable SET DateUploaded='" & Format(Now, "Short Date") & "'"
        cnn.Execute strSQL
    End If

Open in new window

ASKER

Thanks. But got a "Compile error: sub or Function not defined" on:

If Fields("DateUploaded") <> Format(Now, "Short Date") Then

Sorry, the line If Fields("DateUploaded") ... should have a dot in front of the Fields(:

        If .Fields("DateUploaded") <> Format(Now, "Short Date") Then

Open in new window

ASKER

Now got the Run-time error '-2147217904 (80041e10)': No value given for one or more required parameters.

.Open strSQL, cnn, adOpenKeyset, adLockReadOnly

What is the SQL string (strSQL)?

ASKER

strSQL = "SELECT DateUploaded FROM tbl_List"

Do you have a DateUploaded field in you tbl_List table?

ASKER

Yes.

Please post the complete code.

ASKER

Sorry, I put misspell the field name.

Now I got the Run-time error '31519': You cannot import this file.

DoCmd.TransferText acImportFixed, "tblList Link Specification", "tbl_List", "tblLinkTable", True

1. Is the file a fixed width text file?
2. Were you able to import the file manually?

ASKER

1. Yes
2. Yes

You cannot use "tblLinkTable" as the file path and name. You must use something like "C:\tblLinkTable.txt" for the file path and name.

ASKER

Thanks. Everything is working now except got a new table calls: Name AutoCorrect Save Failure."

Object Name Object Type Failure Reason Time
tbl_List Table Could not save the object 5/4/2009 11:45:14 AM

At what point in the code does the error occur?

ASKER

I don't know. I didn't see any error occur, but this table. Also even I already import once today, the import codes still run after I loaded the form.

I recommend you delete the new table and run the code again. Did you delete the two lines of code in the snippet?

DoCmd.TransferText acImportDelim, "SpecificationName", "TableName", "FilePathAndName", True
' OR

Open in new window

ASKER

I have deleted the tbl_List table, and recreate a new one. I got the same "Name AutoCorrect Save Failure: Table"

Object Name      Object Type      Failure Reason      Time
tbl_List      Table      Could not save the object      5/4/2009 12:44:44 PM
tbl_List      Table      Could not save the object      5/4/2009 12:45:18 PM

I have one Docmd.TransferText acImportDelim,

Please add a line of code to your existing code where it says <===Add this line of code.

    strSQL = "SELECT DateUploaded FROM MyTable"
    With rst
        .Open strSQL, cnn, adOpenKeyset, adLockReadOnly
        ' if the data is not today,
        If .Fields("DateUploaded") <> Format(Now, "Short Date") Then
            ' set the update flag to true
            blnUpdate = True
        End If
        .Close  '  <===Add this line of code
    End With

Open in new window

ASKER

Thanks, It works now.

Last question, I want to put the codes to a Module and name is ImportList, so I can just put Call ImportList in each database load form instead of whole codes.

But I got the "Compile error: Invalid outside procedure" on this codes below:

Set cnn = CurrentProject.Connection

So can I call the codes thought Module? If I can, do I have to change anything on the codes?

Thanks in advance.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Thanks. Just want to clarify that if the module name can be same as Public procedure name? As I remember, if the module name is the same as the public procedure name, then call Procedure won't work? Is this true?

I have found that having the module name and the procedure name the same does not work. Remember you are calling the procedure and it does not matter what you call the module. I always use the format basModuleName for my modules (bas = basic module). Some people prefer modModuleName. It really does not matter.

ASKER

Thank you so much for all of your help.

You are welcome. Good luck with your project.

ASKER

Hi,
Just submit another question about transfer import specification into different access 2003 databases.

https://www.experts-exchange.com/questions/24379500/transfer-import-Export-specification-into-access-2003.html

Maybe you can help too!

ASKER

Hi cwood-wm-com,

I got a problem now. Since I placed the same codes on 5 different database startup forms. Sometime, users opens different database at the same time. Then only one use is able to open the form, others got the message The property is not found" and highlight on the codes below:

DoCmd.TransferText acImportDelim, "tbl_List Import Specification", "tbl_List", "\\ShareDrive\", True

So how can I prevent this happen? Should I add codes to ignore the error? Or do you have a better idea?

Thanks,

\\ShareDrive\ should be \\ShareDrive\Path\File.txt

Also, you should use different tables for the different databases.