Linked Tables

[Perissos]

Just a quick question

I have an Access database that when a button is clicked it will open an Excel spreadsheet for entry. When the entry is done it will call an Access function which will run some calculations and update a specific table stored in Access, then it will close the excel spreadsheet.

The excel spreadsheet is only used occassionally to enter some data and nothing is stored in the excel file itself.

If I keep this as a linked table what issues would I run into?

Or is there a better way of doing this?

I tied using an odbc connection and it will write data from access to excel, but I couldn't get it to go from excel to access - so I am looking for other options

 

[KenHigg]

What are you doing in excel that can't be done in Access?

 

[Perissos]

I wanted to set it up in Access, they wanted it in Excel for the moment.

Its going from Excel, to Access -reformatted -then back to Excel. I think its more of a time issue than anything at the moment

 

[KenHigg]

I think I would insist that they do data entry in Access. Case closed. Just my opinion

 

[Perissos]

lol.. I love that opinion.

Unfortunately, I don't get to make the decisions...... yet

For the moment I have it linked and it appears to work great. But I have that feeling like there is something I am not aware of that will cause an issue later.

 

[Galaxiom]

I would run it all in Excel. Open the table as an ADO recordset from Excel. Most of the functions in Excel VBA are similar to Access anyway.

Don't underestimate Excel. In fact some of the stuff in Excel works better. For example a recordset can be applied as a rowsorce with a single command in Excel VBA but you have to use a loop in Access.

Many users are highly skilled in Excel and may not even have Access on their PC. Presenting the application entirely in Excel is far less confronting to them. Excel can present a form that is virtually indistinguishable from an Access form except is says Excel in the taskbar.

For example I have an Excel aplication that populates combos on a worksheet without cellls with lists of documents from an index on SQL Server, opens the text files as streams, parses them using information in tables held in an Access database on a server as it generates a recordset and then drops it all into a single speadsheet. All of it without Access even being installed on the computer. Indeed the mdb file could have been a text file too.

The users all admire the power of Excel yet it is little more than a canvas until the worksheet is generated. My boss loves Excel and it makes him happy when I do stuff without Access.

 

[Perissos]

Thank you for the input

 

[Perissos]

GalaxiomAtoHome, I tried doing this and ran into errors "user defined type not found" I am not sure I can work with ado libraries very well. I think the person I am developing for in on Access 2007,but don't know what OS they have.

Can you suggest a good excel forum to get some help from?

I also have a problem calling a function in access from excel. when I do a get object to run the function, but it actually opened another database in Access 2007 where in 2010 it doesn't.

 

[Perissos]

actually.. never mind. I've decided to go back to accounting.

Thanks for all your help guys.