[RESOLVED] Import Access to Access from Excel

[Fonty]

Is it possible to import a table from an Access' file to another one in Access, with a macro in Excel?

[marktheman]

Hi mate,
Did a search for this, apparently using macros you can import data from an access table to a worksheet in excel and then export this worksheet to another access table.
http://www.exceltip.com/st/Import_da...Excel/427.html
http://www.erlandsendata.no/english/...badacexportado
Hope this helps.

[marktheman]

Please note excel before excel 2007 only contains 65,000 rows while excel 2007 contains 1,005,000 rows. Therefore you cannot copy a whole table from access to excel and then to access again. Do it row by row.
E.g. Open connection 1 to access table
get recordcount from table in access database for connection 1
do while not end of recordcount
read row for access table 1 for connection 1
save row to access table 2 for connection 1 (different recordsets)
end while
close connection 1
Hope this helps.

[RobDog888]

Do the test to see if its 2003 or before and then just do like in my FAQ:
http://vbforums.com/showthread.php?t=402060

[Fonty]

Isn't it just possible to 'call' the Export command in Access so that it export a Table into another Access file?
I see that I could do so in two steps with the examples of these threads. But as I manipulate a large amount of information it becomes quite slow.

[RobDog888]

You could also use the copyobject:
http://vbforums.com/showthread.php?t=409309

[Fonty]

Thanks! The second method looks perfect.
Just one question left. These days I'm working a lot with databases and I feel quite lost. Is there a textbook on vba/ADO/DAO/Excel/Access you would suggest?

[RobDog888]

I wish I could recommend something but I have learned mostly everything on my own. Additionally the Forums have been invaluable in gaining a broad range of real life issues to solve.

[marktheman]

I have a good one for VB6 but it is very similiar to VBA. It's called the "Visual Basic 6, Black Book" by Steven Holzner and is excellent. You can also go into safari books, get 30 day trial and obtain a licence from them.
Cheers.

[Fonty]

I was wondering, if it is possible to run a macro embeded in an Access file from a macro located in an Excel file.

[RobDog888]

I dont think so unless you added a reference to the other and then opened the file.

[Fonty]

How could I do that?

[RobDog888]

Not 100% sure as i havent really got into acutally using the Excel program where I am deep into coding it.

I believe there is code to reference a closed workbook's macro function(s) omewhere in the OD forum. Try a search.

[Fonty]

Hi!
I found this theard:
http://vbforums.com/showthread.php?&...00#post1907700
It's just what I needed. Thanks for your advice. However, this macro calls a macro embeded in the Macro section of Access. The Macro I'd like to run lies in a Module. How can I call a Macro from some Module?