Linking Excel file to a table, instead of importing it (1 Viewer)

brunces · Mar 15, 2007

Friends,

Please, help me with this...

I have two files: main.xls and main.mdb.

main.xls has one single sheet (SheetData)
main.mdb has one table called (tblData)

I have to LINK main.xls (SheetData) to main.mdb (tblData). To do this, I have a form with a button (bttLinkFile).

Code:

Private Sub bttLinkFile_Click()

    DoCmd.TransferSpreadsheet acLink, , "tblData", Application.CurrentProject.Path & "\main.xls", True
    
End Sub

The point is... When I click the button, Access understands that there's already a table called "tblData", so it creates a new table (tblData1), instead of just linking that existing table "tblData" to the Excel file. Shouldn't it just LINK them? I need them to be linked!

If I use "acImport" instead of "acLink", it works. It imports all data from the spreadsheet into the table tblData.

"tblData" has the same fields of "SheetData" (I created tblData linking SheetData for the first time).

So, how can I link them, avoiding Access to create a new table?

Before you ask, I need to link main.xls several times because it's not a fixed file with fixed data. I export main.xls file from another Excel file I work at. So, I eventually have to link it again and again.

I hope you can help me. Thank you for your attention, guys.

Hugs,

Bruce

petehilljnr · Mar 15, 2007

Code:

Private Sub bttLinkFile_Click()

[B]On Error Resume Next
DoCmd.DeleteObject acTable, "tblData"[/B]

[B]On Error Goto Err_Handler[/B]

Private Sub bttLinkFile_Click()

    DoCmd.TransferSpreadsheet acLink, , "tblData", Application.CurrentProject.Path & "\main.xls", True
kFile_Click

[B]Exit Sub
Err_Hander:
msgbox err.description[/B]
End Sub

    
End Sub
Function find_table(strTableName) as Boolean

brunces · Mar 16, 2007

petehilljnr,

Hi, buddy. I'm sorry, but I really didn't understand your corde.

You've put a Private Sub inside another Private Sub with the same name... Else, what about that Function at the bottom?

Sorry, I'm kinda confused. If you don't mind, could you please rearrange it or post it again? I would appreciate it.

There's also one thing I have to tell you... I have queries related to tblData. I've already tried deleting this table before linking the Excel file again, so that it would create a new table with the correct name. But, when I did that, queries lost their relation to the table. Then, when I linked the Excel file again, I had to edit all queries. Fields were still there, but I had to tell them what was their source table again. They had lost it. So, I think I cannot delete the table. I really have to link it to the Excel file without deleting it. Is that possible?

Obs.: I use Access XP.

Still in need of help here.

Thank you all. And thank you, petehilljnr. I'll wait for your new reply.

Hugs,

Bruce

petehilljnr · Mar 16, 2007

Sorry - that was a complete mess:

Code:

Private Sub bttLinkFile_Click()

On Error Resume Next
DoCmd.DeleteObject acTable, "tblData"

On Error Goto Err_Handler

    DoCmd.TransferSpreadsheet acLink, , "tblData", Application.CurrentProject.Path & "\main.xls", True

Exit Sub
Err_Hander:
msgbox err.description
End Sub

Now - your queries will not be stuffed up. The reason they stuffed up before is probably because you opened them when tblData did not exist, therefore it changed the queries (deleted columns where they didn't exist etc) and you probably inadvertantly saved them when you closed the query.

I've done this numerous times - linking in Excel, DBF, CSV files etc hundreds of files one after the other in an automated process, and all using the same queries after deleting the table no problems - as long as the table exists when the queries are opened.

brunces · Mar 16, 2007

petehilljnr,

I can't remember if I ran a query without the table, but I admit this could've happened indeed. And certainly that's the reason why my queries were stuffed up, as you said.

I've tried your last code and it works fine. I really had no problem with it.

Just one more little doubt: I've also created this (below) to tell Access what Excel file will be linked. I'd just like you to tell me where, in the code, should I put it, I mean, the best place (line). Else, if I hit ESC during the InputBox, it should not delete the table and just exit sub. How?

I'm not very familiar with these "On Error" things!

Code:

Private Sub bttLinkFile_Click()

    Dim strChooseFile As String

    strChooseFile = InputBox("Type the file name:", "File to be linked")

On Error Resume Next
    DoCmd.DeleteObject acTable, "tblData"

On Error GoTo Err_bttLinkFile_Click

    DoCmd.TransferSpreadsheet acLink, , "tblData", Application.CurrentProject.Path & "\" & strChooseFile & ".xls", True

Exit_bttLinkFile_Click:
    Exit Sub

Err_bttLinkFile_Click:
    MsgBox Err.Description
    Resume Exit_bttLinkFile_Click
    
End Sub

Thank you very much for what you've already helped me.

Hugs,

Bruce

Linking Excel file to a table, instead of importing it (1 Viewer)

brunces

Registered User.

petehilljnr

Registered User.

brunces

Registered User.

petehilljnr

Registered User.

brunces

Registered User.

Similar threads

Users who are viewing this thread