Removing link between Access & Excel

The New Boy

New Member
Joined
Oct 5, 2006
Messages
6
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?
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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

New Member
Joined
Oct 5, 2006
Messages
6
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

New Member
Joined
Oct 5, 2006
Messages
6
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251

ADVERTISEMENT

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

New Member
Joined
Oct 5, 2006
Messages
6
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

New Member
Joined
Oct 5, 2006
Messages
13

ADVERTISEMENT

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

New Member
Joined
Oct 5, 2006
Messages
6
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

New Member
Joined
Oct 5, 2006
Messages
6
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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
 

Forum statistics

Threads
1,136,309
Messages
5,674,999
Members
419,541
Latest member
freddyboots

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top