Open mdb file from another computer

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

stefantem - 19 Jan 2006 13:04 GMT

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

Signature

stefantem

Reply to this Message

tony h - 19 Jan 2006 15:03 GMT

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

Signature

tony h

Reply to this Message

stefantem - 20 Jan 2006 19:35 GMT

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

Signature

stefantem

Reply to this Message

tony h - 24 Jan 2006 09:30 GMT

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

Signature

tony h

Reply to this Message

tony h - 24 Jan 2006 09:35 GMT

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

Signature

tony h

Reply to this Message

stefantem - 24 Jan 2006 10:31 GMT

And if BD has a password?

Signature

stefantem

Reply to this Message

tony h - 24 Jan 2006 11:52 GMT

Set db = DAO.OpenDatabase(Name:="C:\_pms\dbforum.mdb", _
Connect:="MS Access;PWD=mypassword"

Reply to this Message

tony h - 24 Jan 2006 11:57 GMT

Actually I think you may need to list the other "optional" parameters as
in :

Set db = DAO.OpenDatabase("C:\_pms\dbforum.mdb", false,true,"MS
Access;PWD=mypassword")

Sorry havn't got time to test it. I seem to remember that the optional
parameters become mandatory when using connect.

Well that's what keeps life interesting.

Signature

tony h

Reply to this Message

stefantem - 25 Jan 2006 07:02 GMT

Before passwording the database I need to declare a function connect()
that opens the database and than db will be recognized outside the
function. I want to use the function in every commandbutton code or
just once, when opening the workbook that contains those buttons. But I
don't know exactly how to do that. Can you help me?

Signature

stefantem

Reply to this Message

tony h - 25 Jan 2006 11:24 GMT

If the buttons are on a userform then declaring the db outside the main
structure should keep the object persistant.

If the buttons are on a worksheet then when the code execution stops
persistance will be lost.

There is a way to get apparant persistance and that is to declare a
global class module using "as new". This means that any reference to
the class module will create an instance if one does not already
exist.

Then you open the database in the intiialise event and return a
reference to the database object. This also gives a rather nice feature
that you can close the database in the terminate event thus ensuring
references to the database are tidied up in the event of unexpected
code ending.

I will post some code later.

Signature

tony h

Reply to this Message

tony h - 25 Jan 2006 11:48 GMT

AS with all these sort of things Error handling is necessary and should
be used.

PART1====================== goes in a standard module
Option Explicit

Public glb As New myGlobalsClass

==========================

PART2 ============ this can go anywhere ==========
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")
Set DB = glb.DB
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

PART3 ============= This as a Class module called myGlobalClass
Option Explicit

Dim DB1 As DAO.Database

Private Sub Class_Initialize()
Set DB1 = DAO.OpenDatabase("C:\_pms\dbforum.mdb")
End Sub

Public Function DB() As Database
Set DB = DB1
End Function

Private Sub Class_Terminate()
DB1.Close
Set DB1 = Nothing
End Sub

Signature

tony h

Reply to this Message

stefantem - 25 Jan 2006 12:29 GMT

It works. Thanks a lot.
Now I will test to password the data base.

Signature

stefantem

Reply to this Message