To Access and Back

[bjoshi]

I have a access file at "c:\temp.mdb" its password protected, lets say the password is "xxx".
The mdb file has 2 tables, table1 and table2 both tables have the same variables,
1.Autonumber
2.FirstName
3.LastName
4.Date

I have an excel sheet with 2 columns (Firstname,LastName) and there is just 2 rows of data in it starting from range(A2 to B3)

I want the values of the first row (A2:B2) (FirstName,Lastname) to be updated in Table1 of the mdb file.
Then, I want the values of the second row (A3:B3) (FirstName,Lastname) to be updated in Table2 of the mdb file.

Also, in both tables (table1,table2) I need the current system date and time to be updated in the 4th variable "Date".

After this, I need to get back the complete data (Autonumber,FirstName,LastName,Date) from both the tables in ranges (A5 to D6).

This sounds lame, but I didnt want to start 2 threads one for import and other for export. Also I am using Office 2003, will this be different for Office 2010?

Thanks,
bjoshi

[carrellim]

Is there a certain reason why you want this in excel?
Any type of function or macro can be written in vba in access and then any output you get can be exported to excel.

Or are you just trying to skip a step.
Please clarify

[bjoshi]

Hi,

I need this in excel because not everyone has access installed on their workstation, but everyone definitely has excel installed, as a part of our company policy.

[carrellim]

I have a access file at "c:\temp.mdb" its password protected, lets say the password is "xxx".
The mdb file has 2 tables, table1 and table2 both tables have the same variables,
1.Autonumber
2.FirstName
3.LastName
4.Date

I have an excel sheet with 2 columns (Firstname,LastName) and there is just 2 rows of data in it starting from range(A2 to B3)

I want the values of the first row (A2:B2) (FirstName,Lastname) to be updated in Table1 of the mdb file.
Then, I want the values of the second row (A3:B3) (FirstName,Lastname) to be updated in Table2 of the mdb file.

Also, in both tables (table1,table2) I need the current system date and time to be updated in the 4th variable "Date".

After this, I need to get back the complete data (Autonumber,FirstName,LastName,Date) from both the tables in ranges (A5 to D6).

This sounds lame, but I didnt want to start 2 threads one for import and other for export. Also I am using Office 2003, will this be different for Office 2010?

Okay first off - -
Linking to excel with an automatic update just isn't your best option here. So unless you wanna get a pro to write you a vba script, The best thing for you to do would be to use the "Analyze with excel" under Microsoft Tools at the top (maybe that's the name it's been a while since 2003) And this should be standard for 2010 with the Ribbon changing the arrangements of icons.

So
1)Just run a simple query of your table and concatenate the FirstName& "," & LastName together so the create 1 item.
- If you can't do this step I would seriously recommend getting the access 2003 bible at half price books for like 10 bucks. It will save you headaches and enlighten you on SQL and Queries.
2)Now that your query concatenate the names, add in the second column of the query an 'expression' (aka function) =Now()
3)Run it. This should give you the names together and the date.
4)Click at the top and analyze with excel and it should create a file for you.
5)Use that file or copy it over.

That's it. Simple yet frustrating for a non-access user.