Help with .db linking to a spreadsheet

  • Thread starter Thread starter mike
  • Start date Start date
M

mike

Hi,
Ok, here is the problem that I am having which I hope someone can help me
out with. I am using ms xcel 97 and ms access 97. What I would like is a .db
file where I just can enter the date time and a value and that it will go
automatically to my spreadsheet and if I change a value it will
automatically update the spreadsheet. Sounds easy but I am have no luck.
Here is a example:
Date time day evening night
6/1/04 0:00 300
The above is what I would just like to enter and then I want it to look the
same in the spreadsheet.
Here is what I tried.
1) Went to access and created a blank .db
2) Then I created a new table using the data sheet view. In field one I type
in date, field 2 time, field 3 day field 4 evening, field 5 night. Next row
I typed in field one 6/1/04, field 2 0:00, field 3 0, field 4 0, field 5
300.
3) I then saved it as a table1.
4) I tried both to create a primary key and it did not make a difference.
5) I then highlighted the new table and went to new and then link table.
6) I then picked my my spreadsheet file .xls which I already created.
7) Next I exited out and saved everything.
I next tried to add some more data and it just did not update my
spreadsheet.
If someone can tell me what I am doing wrong it sure would be appreciated.
 
1. Open Access
2. Choose File ... Get External Data
3. Choose Link
4. Change the file type to Excel in the resulting dialog.
5. Find and open the proper Excel file and sheet.
6. Treat the linked Excel spreadsheet exactly like a table.

Note: The spreadsheet should look like a table for this to be successful.
You cannot have headers that cross columns, or totals, etc. in the
spreadsheet.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Hi Mike,

It is possible to get done what you seem to want to do but there is a lot
more work than luck involved. Be aware that what you are trying to
implement is called Automation. Look it up in Access Help.

First, you need to design your application from the top down and write down
your design. Next, decide what functions will be done in which Microsoft
application (Access and Excel) space. Design each piece.

If you don't do a good job designing and writing specifications you'll have
a hard time doing the rest and you'll never know when you're done.

When you start to actually do start doing something toward coding, Start on
the Excel side and maybe record a macro to get started. You may have to
make several cuts at it to get something you like. That macro, just VBA
code really, becomes a skeleton that you will expand greatly and generalize
a bunch so that the only thing hard wired is some test data in some
variables. That part will only be done when the spreadsheet it produces
looks pretty much like you want your production spreadsheets to look like.
At that point copy the VBA code over to your Access MDB.

Before you do any Access code, write some of the automation code to declare
an object as an Excel application and open or create your spreadsheet and do
the things you're doing and then close the spreadsheet and Excel and set
your object to - Nothing. Again, the spreadsheet should look as you want
the finished product to look. When you can do that much from the Access
side you are ready to begin coding the rest of your Access design.

When the Access side looks good, integrate the imported and enhanced VBA
from Excel with your Access code. Polish it a bit and you're done! Of
course, you're also a few months older.

There are lots of Excel newsgroups with experts who will help. For your
apparent needs I recommend microsoft.public .excel.programming. There are
lots of others. For Access I recommend that you start by lurking
microsoft.public.access.gettingstarted Again, there are lots of other
groups there too.

Before asking for help, please try to solve your own problems. Buy books
and read them. If you've tried to solve the particular issue you face and
have hit a brick wall, ask for help. When you do, please confine each post
to a single issue and tell us your OS, version of Access and what you've
already done to try to solve the problem.

People are generally happy to help those who are obviously trying to help
themselves and are somewhat less charitable with omnibus, "Please tell me,
in detail, everything I need to know about [Access, Excel, VB, Word, etc.]".

A terrific resource for Access developers is at www.mvps.org/access Take
some time and Prowse it all.

HTH
 
Back
Top