excel locked if linked to access table.

[lody0080]

Hi, I have an access 2007 database table linked to an excel spreadsheet. however, if the database is opened, I cannot open the excel spreadsheet. it says the file is in use. If i open the excel spreadsheet before i open the database, the file doesn't allow me to save. I want to allow users to access the database (not modifying), while I could be updating the spreadsheet. Is this possible? I have researched the issue and found no solutions.
these are stored in a network.

thanks in advance.

 

[AlienRay]

That's absolutely correct. Linking to an Excel spreadsheet will definitely impact your ability to open and save changes. My preferred solution is to import the data I need into an Access table (usually using the TransferSpreadsheet method). Importing the data will not lock the source in the same way that linking will.

 

[lody0080]

that is painful. the spreadsheet itself requires monthly updates that involves pulling data from another report (vlookup) and there are formulas on the spreadsheet. So simply importing data into the database would be a hassle. Does anyone know a better way for this?

 

[AlienRay]

I think you're overestimating the hassle involved. If you've got a defined name range for the data you want in your Excel sheet, we're talking about three or four lines of code. You either trigger it when the database opens, or put in a button that allows your user to refresh the data as needed.

You need one RunSQL line to clear your Access table, and a TransferSpreadsheet to bring in the new data. Your users wouldn't even know it was happening.

 

[lody0080]

its good to know there are better solutions out there. I am not familiar with sql. can you elaborate what exactly i have to do?

 

[AlienRay]

Step 1 is to decide when you want this data to refresh. Do you want it to happen every time someone opens the database, for example, or should it only happen when a certain Form or Report is opened, or should it only happen when a user clicks on a specific button?

Whatever the trigger, you'll have some VBA code triggered by that action. Let's say you want this refresh to happen any time your main switchboard form is opened. In either the On Open or On Load event for that form, you would put in code that looks something like this:

DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE myTargetTableName.* FROM myTargetTableName;"
DoCmd.TransferSpreadsheet acImport, , "myTargetTableName", myExcelFilePath, True, myExcelNamedRange
DoCmd.SetWarnings True

You would need to change the variables myTargetTableName, myExcelFilePath and myExcelNamedRange to whatever your actual parameters are.

Putting this in the On Open event would mean it would happen automatically, and the SetWarnings lines mean it would be transparent to your users. They might notice a slight delay as the code ran, depending upon how many records are being imported, but no action would be required of them.

 

[lody0080]

works like a charm. thanks so much.

 

[lody0080]

What happens when a new header or column is added to the source spreadsheet.

I encounter error which tells me this new field does not exist in the destination table.

Is it possible to also automatically add the new field intead of having to add it manually in order for it to import the table?

 

[smig]

do you need these new columns ?
if not you can import the excel into a new TEMP table, and run another query to take the data you need from this table to the correct table.
when you done delete the TEMP table...
next refresh ... re import into a newly created TEMP table...

deleting the table you realy need is not a good idea cause it will also delete the relationships.

 

[lody0080]

I do need the new columns should they be added to the source. IF i create a temp table and the query to copy results to destination table, will the destination table be able to pick up new columns?

I will also some tips on the codes...programming newb here: )

Thanks.

 

[smig]

no. if you need the new columns you must add them to your table.
if your table is not linked as part of the relationships (it can be still be linked in queries) you can delete it and re import.
you can even re import as new table naming with the existing table name and it will over run the old table

 

[lody0080]

yes, they are linked in queries so I am okay with deleting the old table.

so i tried the DROP Table command:

DoCmd.SetWarnings False
DoCmd.RunSQL "Drop Table tblFacilities;"
DoCmd.TransferSpreadsheet acImport, , "tblFacilities", "\\CONNTDFS1.CON.CHEVRONTEXACO.NET\SHARE\CHVSVC\CFC\Upstream Accounting\Cost Allocations\Facilities\Capital Recovery\Identify CR situations\Capital Recovery tracking.xlsx", True, "CR 4Q09!A1:BD847"
DoCmd.SetWarnings True

but I get a runtime 3211 error saying I could not locked "tblFacilities"