Access forms appear blank when data connected to excel is open

[mulamootil]

Hi - I have a spreadsheet that I imported from an access database, so in other words, it is connected to an external database. The database is split and when I try to open the front end while this spreadsheet is open, I do not see any content in my forms. It all looks good when I close the excel spreadsheet and then open the front end. I am not sure what I am doing wrong.

Both the database (back end) and the spreadsheet are located on network shared drive.

Do you guys have any suggestions as what is possibly happening here.

Thanks,
Stan

PS: I am going to post this question on an excel forum too.

[jimatqsi]

Are you opening the sheet in a form that will do any updates? You can't open a sheet for update in two different programs at the same time.

If you want to open the sheet to view only, try changing the form's data query to ReadOnly, that might solve this.

Or you might want to add some error checking so that the problem can be recognized and a message box is displayed telling "Sorry, the sheet is busy now"

If this is insufficient, please post some code from the program with the problem.

Jim

[mulamootil]

Hi Jim - I am not opening the sheet in a form. This is what I am doing.
The only application that I have open on my desktop to begin with is an excel spreadsheet. So on a blank sheet, I import an access table (BE located on the network) by going to Data tab and external data group. I select the table that I want and the sheet is now populated with access table. While this sheet is still open, I am opening the form (front end)located on my desktop. I am able to see the forms which has buttons, however I do not see any contents on forms which has combo boxes and text boxes and such.

I want to open the sheet to create pivot tables and charts. I am not sure how to change the data query to read only as you suggested.

Another thing I noticed, when I click on a form which ultimately opens with no content takes a long time to open and it displays an error message on the bottom status bar "this record set is not updateable"

I asked another user to import data into a sheet from access be and he is able to view all the forms. In my case when I have the sheet open, other users whom I have distributed the front end are facing the same problem of not seeing any content in forms which has combo boxes, text boxes etc...

I am not sure if I have given you enough information to trouble shoot this further. Thanks again.

[NeoPa]

If Excel has the file open then that file is locked. Solid. You won't have simultaneous access from Access. It just aint gonna happen.

[jimatqsi]

Au contraire, mon ami ... I have spent the last 45 minutes building Access forms and queries and Excel sheets, and making them import, or view each other ... and I never get a "locked out" status. I can even have a single table open in both programs and update a cell and see it update immediately in an Access form when I switch back to that window.

I am working in Office 2003. I have not even had to resort to snapshot queries or do anything else to avoid record locking conflicts, of course that depends some on the type of linkage I did.

On the Excel side I did "Olap Cube" and "Microsoft query" and it doesn't seem to matter. On the Access can be directly in the table or working in a form or looking at a query, and all seem to work okay.

Hmmmm ....

Jim

[NeoPa]

Well. I stand corrected Jim (Sitting down actually - but who's counting).

Sorry for posting on something I wasn't as sure about as I thought I was.

Please ignore my post in that case.

[jimatqsi]

Ade, I wish I knew what it meant ... or why the OP is seeing this problem. Maybe we need to refer this to some other experts.

Stan, could you tell us more about your situation? What version of Access/Excel are you working with? What is the nature of the connection between Excel and the Access table? I mean what are all the choices you make after you click the Data Tab and then Get External Data? What is the Access form's underlying query like? What if you go directly into the Access table or query instead of the form, do you have the same problem? Is the table holding the data really an Access table or is it something else that your front-end is linked to?

Jim

[NeoPa]

I don't think I can help much Jim :(

I think my earlier confusion was from remembering a situation where I was trying to update a spreadsheet via Application Automation. It didn't like opening a spreadsheet in Excel that was already open in Excel within another session.

As for locking in this case, I don't know if you've explored all the various multi-user/locking options in Access Options. There might be something there that has a bearing on the matter.

[jimatqsi]

Well, Ade, I thought you were on to something. But I just changed my form to lock all records and the only change I saw was that if the Access form was open when the Excel query was started, then the Excel sheet was read only. But if Excel started first, then both could do an update to the data.

We'll just have to wait for more details from the original poster.

Jim

[mulamootil]

Jim/NeoPa - I am using Office 2007 with Vista. After clicking data tab and then external data, I select the database, which gives me a list of all the available tables. I pick the one that I want which is followed by 'Import data' window. Here I select 'table' for how I want to view the data in the workbook, select the first cell of the existing sheet for the data to be put. Then I click 'ok' and it imports all the data from the access table that I selected.

I have been trying few things since yesterday. For the sake of testing, I created a new db (without splitting) with few tables and a form. There are many bound controls, hence using so many tables (I am pretty sure that it is not the most efficient way creating a db!!). So next I opened an excel sheet and imported the access table of the newly created db. While the sheet was still open, I tried opening the db on my desktop, and it gave me this message " Read only - This database has been opened read only. You can only change the data in linker tables. To make design changes, save the copy of the database ...save as". When I click on the form, again I do not see any controls. The only thing I see is the name of the form that I appear on the top in the shaded area.

Thanks,
Stan

[mulamootil]

Another thing that I noted, when I reopen the sheet, I get this warning, whether to enable the data connections (dc). So I go ahead and enable the dc. At this point, I am able to view both the form and sheet simultaneously. However, even after enabling the data connection, I do not see any updates that I made in the access table via the form. in order to see the updates, I click on 'refresh' on the data tab in the sheet and get this message if the db is open.." you try to open db that is already open...try opening when it is available". So now when I close the db and try to refresh the sheet, I am able to refresh the sheet and I see the recent entries in the db. However, with the sheet open, I am able to open the db as read only and unable to see anything on the form.

[jimatqsi]

I have not done enough in Office 2007 to be able to answer about this.

Jim

[mulamootil]

access forms appear blank when data connected to excel is open

I figured out what was causing the problem here. After exporting the db table into excel, I edited the mode to 'read write' from 'read only' in the connection string. That did the trick. It's all good now. Thanks for everyone's help.