Excel to Access

[dmac]

Can I write a macro that will export one row of data (A2:X2) to a table in
Access automatically? If so, can I do that with multiple spreadsheets of the
same ilk going to the same table in Access? I have the summary page in Excel
laid out in a database type of style for ease in conversion to Access.
I originally tried using "Paste Link" from the summary page of the
estimating spreadsheet we use to a master summary page of all jobs and then
linking that page to Access. This is a little flimsy and unreliable with
non-computer friendly people using it. I need something idiot proof that
will work on its own.
I would appreciate any help you could give me, Excel Gods.

 

[Jim Thomlinson]

There is nothing truely simple here... You can write to an Access database
via an ODBC connection which is not that terribly diffictult to do. The trick
is in making it automatic and ensuring that you do not post the same info
multiple times. Depending on your database you may also need to worry about
referential integrity. In any case if you are looking for the magic button to
push there isn't one. If you are not scared off by talk of ADODB recordsets
and such then post back and we can point you at some resources to get you
started...

 

[dmac]

I've got about a month to figure this out so point away. I'm not sure what
you mean when you talk about ODBC connection and ADODB recordsets, but I'll
learn.
Thanks

 

[ryguy7272]

This is worth a look:
http://www.exceltip.com/st/Export_d...s_(ADO)_using_VBA_in_Microsoft_Excel/425.html

This is good too:
http://www.programmingmsaccess.com/TheBook/ADOExcerpt/ADO Overview.htm


Regards,
Ryan--