Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open mdb file from another computer
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open mdb file from another computer
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open mdb file from another computer
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open mdb file from another computer
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open mdb file from another computer
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open mdb file from another computer
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open mdb file from another computer
Set db = DAO.OpenDatabase(Name:="C:\_pms\dbforum.mdb", _ Connect:="MS Access;PWD=mypassword" -- tony ----------------------------------------------------------------------- tony h's Profile: http://www.excelforum.com/member.php...fo&userid=2107 View this thread: http://www.excelforum.com/showthread.php?threadid=50286 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open mdb file from another computer
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. -- 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open mdb file from another computer
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? -- stefantem ------------------------------------------------------------------------ stefantem's Profile: http://www.excelforum.com/member.php...o&userid=13594 View this thread: http://www.excelforum.com/showthread...hreadid=502862 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open mdb file from another computer
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. -- 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open mdb file from another computer
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 -- 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open mdb file from another computer
It works. Thanks a lot. Now I will test to password the data base. -- stefantem ------------------------------------------------------------------------ stefantem's Profile: http://www.excelforum.com/member.php...o&userid=13594 View this thread: http://www.excelforum.com/showthread...hreadid=502862 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't open a file on one computer but can on another | Excel Discussion (Misc queries) | |||
Excel file won't open on only one computer | New Users to Excel | |||
same file, new computer, will not open | Excel Discussion (Misc queries) | |||
my file's format changes when i open the file on another computer | Excel Discussion (Misc queries) | |||
How do I open an xls file if I don't have Excel on my computer? | Excel Discussion (Misc queries) |