I want to open and work (read only) with an Access database from another
computer. How can I do this?


--
stefantem
------------------------------------------------------------------------
stefantem's Profile: http://www.excelforum.com/member.php...o&userid=13594
View this thread: http://www.excelforum.com/showthread...hreadid=502862

You have a number of options depending on how you want to handle it.

1. you can open the database in access and then save a table or query
as a spreadsheet

2. In Excel use Data ... Get External Data .. New Database Query ...
Microft Access database. If you use the record new macro feature this
will give you enough to understand most of the VBA.

3. You can create a reference (in VBA tools ... references) to either
a DAO or ActiveX Data Object library which will then give you access to
the programming objects to open and select data. A useful method in
this is the "copyfromrecordset" method. You may get more help on this
from an Access site than from this Excel site

Hope this points you in the right direction. When you know which you
want to do then update this and I will find some code.

regards


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=502862

I need to use the third option. Can you give me some code example?


--
stefantem
------------------------------------------------------------------------
stefantem's Profile: http://www.excelforum.com/member.php...o&userid=13594
View this thread: http://www.excelforum.com/showthread...hreadid=502862

This code opens the database gets the data and pastes it onto the
spreadsheet. It is referencing the Microsoft DAO 3.6 Object Library
although others will work and you may prefer to use ActiveX Data
Objects.

Sub a()
Dim db As DAO.Database
Dim rst As Recordset

Dim strSQL As String

Dim rng As Range

Set db = DAO.OpenDatabase("C:\_pms\dbforum.mdb")

strSQL = "select CLNAM1,CLNAM2 from _client where CLSORC<"""";"
Set rst = db.OpenRecordset(strSQL)

Set rng = ThisWorkbook.Worksheets(1).Range("A1")
rng.CopyFromRecordset rst

Set rst = Nothing
db.Close
Set db = Nothing
MsgBox "done : " & strSQL
End Sub


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=502862

Sorry forgot to add that you can use the query builder in access to
model the SQL or set up queries in the access database to simplify the
code

Have fun


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=502862

And if BD has a password?


--
stefantem
------------------------------------------------------------------------
stefantem's Profile: http://www.excelforum.com/member.php...o&userid=13594
View this thread: http://www.excelforum.com/showthread...hreadid=502862