Removing link between Access & Excel

[The New Boy]

I have created an Access front end on to an Excel spreadsheet.

I created a Primary key at a late stage of the build and cannot get Access to save the Primary key as an Auto Number.

I also have problems that certain field formats in Access don't work because of conflicts with Excel.

I have deleted the linked table from Access then changed the format in Excel. I then got External data and linked Access to the same updated Excel spreadsheet. This didn't work.

How can i change formatting in Access when it won't let me save the correct disciplines in Table Form (Access) because i can't save when the Access / Excel tables are linked?

 

[SydneyGeek]

Which version of Access are you using? If it's XP or higher you will most likely find that you can't edit the linked Excel data.

Also, Access treats external data sources differently from internal ones. You can't change the structure of a linked file -- even a linked Access table, so you will be unable to create Autonumbers on the Excel file.

If you want to create an Autonumber primary key, the data will need to be in a local Access table, which requires an import. This changes Access to the data entry environment, and Excel to being a place where you can view and report on that data.
Alternatively, if the data entry will be done in Excel, you will need to create a routine that automatically increment IDs. So... is the workbook mostly a data repository, or does it contain a lot of calculation logic as well?

Denis

 

[The New Boy]

I am running Microsoft Access & Excel 2002 SP£ on Windows XP.

The database is for customer information input through Access with my accounts pulled out through Excel

 

[The New Boy]

I have just imported the data from Excel as opposed to Linking tables.

This enables me to create a Unique Identifier with AutoNumber.

Unfortunately I still have issues with some Date format.

Also, how do I link it back to Excel as I was under the illusion that this was the best method for pulling out my accounts.

As it stands, with the information being imported as opposed to linked any new records created in Access are not automatically added in to the Excel spreadsheet.

Very grateful for all of your help

 

[SydneyGeek]

There's a couple of ways to get the data back into Excel.

Possibly the easiest to set up is to use MS Query (Data > Get External Data > New Database Query).
You will need to click Add, select the Access driver, browse to the database, and give the link a name. At that point you will see a list of queries and tables -- browse to the table, select the fields you want to view, click OK, and return values to Excel.
You should also have the option to fill formulas down. This is useful if you create formulas in the worksheet.
Viewing the new records is is then as easy as refreshing the query (place your cursor somewhere in the data table, then Data > Refresh data).

Denis

 

[The New Boy]

OK right......

I had a few hiccups but i've done what you said.

As it stands now I have achieved everything that i wanted to (and knew that i could) in Access...... ie auto number ID and correct field formats.

I also created a new database query in Excel and pulled all the info back into Excel and have refreshed the data as well.

The problem that i have is that I would like;

1. Access and Excel to update each other automatically
2. To be able to save formulae in Excel (lost each time i refresh...)
3. To be able for the formulae saved and working in Excel to automatically feed cells in Access.

The main reasons for these requests are so that it is easy to see in Access a total invoice figure (calculated as the sum of four separate fields in Excel). Also to do Accounts in Excel.

 

[dsdavids]

You can add formula beside the range that the query is imported to and there is option to copy down that formula with all new data.
Updating Access, so far as I know needs to be done via ADO of DAO in code. You can add the process to an event sub or to a control's event sub as applicable.

 

[The New Boy]

Hey Dean,

I've tried various things but don't quite understand how to carry out the last suggestion.

What exactly do you mean by range? (i have imported a whole database into a new Excel spreadsheet)

How do i add the formulae?

Thanks for being so helpful... it's very much appreciated

Steve

 

[The New Boy]

Quick question on Excel formula

If i want column D to be a sum of columns A+B+C, what formula do i use?

examples Cell D2 =Sum(A2+B2+C2)

Cell D1001 =Sum(A1001+B1001+C1001)

This works but i need to add an extra bit of formula to make this carry on throughout column D with no prompting

 

[SydneyGeek]

To automatically fill down columns, do this:

1. Right-click any cell in the data range and select Data Range Properties.
2. In the dialog that pops up, check the option in the bottom left corner that says Fill down formulas in columns adjacent to data. Click OK and you're done. As long as you have formulas in adjacent columns (like your =SUM(A2:C2) formula), they will fill down the length of the table when you refresh the data.

Denis