VBA to open a .mdb file
 

Hello programmers!

I want to use vba code to open a Microsoft Access File.

Below is the path. I tried to record the action, but it didn't work.

I:\Documents\SHIFTLOG\Shiftlog with Prod.mdb

Thanks,
EMoe


--
EMoe
------------------------------------------------------------------------
EMoe's Profile: http://www.excelforum.com/member.php...o&userid=23183
View this thread: http://www.excelforum.com/showthread...hreadid=495854

VBA to open a .mdb file
 

Have you looked at using "Shell" ?

Tim

--
Tim Williams
Palo Alto, CA


"EMoe" wrote in message
...

Hello programmers!

I want to use vba code to open a Microsoft Access File.

Below is the path. I tried to record the action, but it didn't work.

I:\Documents\SHIFTLOG\Shiftlog with Prod.mdb

Thanks,
EMoe


--
EMoe
------------------------------------------------------------------------
EMoe's Profile:
http://www.excelforum.com/member.php...o&userid=23183
View this thread: http://www.excelforum.com/showthread...hreadid=495854

VBA to open a .mdb file
 

Hi EMoe,

Congratulations! You are about to enter the wonderful world of DAO or Data
Access Objects. DAO is how microsoft exposes Access objects to VBA. The
steps are such:

1. Make sure DAO is referenced within your VBA project. Go to Tools -
References - and select Microsoft DAO 3.x Object Library
2. Once this is open you will be able to insert the objects and methods that
allow you to control Access within VBA.
3. The programming from this point is actually quite elaborate and requires
some experience and knowledge of databases. I suggest consulting the online
help as it is actually very good.

With that said, here are the very simple steps that will at least "Open" the
mdb:

Dim wrkJet As Workspace
Dim dbsNorthwind As Database

Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)

' Open Database object from saved Microsoft Jet database for exclusive use.
Set dbsNorthwind = wrkJet.OpenDatabase("Northwind.mdb", _
True)

Good luck!

M C Del Papa

"EMoe" wrote in message
...

Hello programmers!

I want to use vba code to open a Microsoft Access File.

Below is the path. I tried to record the action, but it didn't work.

I:\Documents\SHIFTLOG\Shiftlog with Prod.mdb

Thanks,
EMoe


--
EMoe
------------------------------------------------------------------------
EMoe's Profile:
http://www.excelforum.com/member.php...o&userid=23183
View this thread: http://www.excelforum.com/showthread...hreadid=495854

VBA to open a .mdb file
 

Do you want to access the data in the mdb file, or just start the database
within an Access instance?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"EMoe" wrote in message
...

Hello programmers!

I want to use vba code to open a Microsoft Access File.

Below is the path. I tried to record the action, but it didn't work.

I:\Documents\SHIFTLOG\Shiftlog with Prod.mdb

Thanks,
EMoe


--
EMoe
------------------------------------------------------------------------
EMoe's Profile:
http://www.excelforum.com/member.php...o&userid=23183
View this thread: http://www.excelforum.com/showthread...hreadid=495854

VBA to open a .mdb file
 

did. DAO is obsolete and new users should use ADO.

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


M C Del Papa wrote :

Congratulations! You are about to enter the wonderful world of DAO or
Data Access Objects. DAO is how microsoft exposes Access objects to
VBA. The steps are such:

VBA to open a .mdb file
 

EMoe:

As keepiTcool points out, ADO is the newest
(really not that new) and the recomended way
to go by people that recomend things like that.

Here is a procedure that willl get you statrd in that direction.

Good Luck
TK

Private Sub CommandButton4_Click()

On Error GoTo ErrHandler

Dim rg As Range
Set rg = ThisWorkbook.Worksheets(2).Range("a1")

'To use ADO objects in an application add a reference
'to the ADO component. From the VBA window select
'Tools/References< check the box
' "Microsoft ActiveX Data Objects 2.x Library"

'You must fully quality the path to YOUR file

Dim db_Name As String
db_Name = ("C:\Program Files\Microsoft Visual Studio\VB98\NWind.mdb")
Dim DB_CONNECT_STRING As String

DB_CONNECT_STRING = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"data Source=" & db_Name & ";" & ", , , adConnectAsync;"

'Create the connection
Dim cnn As New ADODB.Connection
Set cnn = New Connection
cnn.Open DB_CONNECT_STRING

'Create the recordset
Dim rs As ADODB.Recordset
Set rs = New Recordset

'Determines what records to show
Dim strSQL As String
strSQL = "SELECT CompanyName, ContactName, City, Country " & _
"FROM Customers ORDER BY CompanyName"

'Retreive the records
rs.CursorLocation = adUseClient
rs.Open strSQL, cnn, adOpenStatic, adLockBatchOptimistic

'Test to see if we are connected and have records
Dim num As Integer
num = rs.RecordCount

Dim num1 As Integer
num1 = rs.Fields.Count

If cnn.State = adStateOpen Then
MsgBox "Welcome to! " & db_Name & " Records = " & num & " Fields =
" & num1, vbInformation, _
"Good Luck TK"
Else
MsgBox "Sorry. No Data today."
End If

'Copy recordset to the range
rs.MoveLast
rs.MoveFirst
rg.CopyFromRecordset rs
rg.CurrentRegion.Columns.AutoFit

'close connection
cnn.Close
Set cnn = Nothing
Set rs = Nothing

Exit Sub

ErrHandler:
MsgBox "Sorry, an error occured. " & Err.Description, vbOKOnly
End Sub

"EMoe" wrote:


Hello programmers!

I want to use vba code to open a Microsoft Access File.

Below is the path. I tried to record the action, but it didn't work.

I:\Documents\SHIFTLOG\Shiftlog with Prod.mdb

Thanks,
EMoe


--
EMoe
------------------------------------------------------------------------
EMoe's Profile: http://www.excelforum.com/member.php...o&userid=23183
View this thread: http://www.excelforum.com/showthread...hreadid=495854

VBA to open a .mdb file
 

Thanks for all the input.

I just want to open an Access File. The file when opened has severa
buttons that goes to a *front page form*, where I can click an icon t
proceed further to a page in Access.

I have absolutely no experience with Access. I thought that th
programming for excel, to open such a file, would be a simple code ad
in. Looking at some of the suggestions here, I guess it's a little mor
differcult; but I'm willing to try.

Thanks Again,
EMo

--
EMo
-----------------------------------------------------------------------
EMoe's Profile: http://www.excelforum.com/member.php...fo&userid=2318
View this thread: http://www.excelforum.com/showthread.php?threadid=49585

VBA to open a .mdb file
 

then

Shell "c:\path\mydb.mdb"

should do the trick

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


EMoe wrote :


Thanks for all the input.

I just want to open an Access File. The file when opened has several
buttons that goes to a *front page form*, where I can click an icon to
proceed further to a page in Access.

I have absolutely no experience with Access. I thought that the
programming for excel, to open such a file, would be a simple code add
in. Looking at some of the suggestions here, I guess it's a little
more differcult; but I'm willing to try.

Thanks Again,
EMoe

VBA to open a .mdb file
 

Hi Keep it cool!

Here is what I set up, but it didn't work.

Sub OpenLog ()
Shell "I:\Plant\SHIFTLOG\Shiftlog with Prod.mdb"
End Sub

Is this right?

EMoe


--
EMoe
------------------------------------------------------------------------
EMoe's Profile: http://www.excelforum.com/member.php...o&userid=23183
View this thread: http://www.excelforum.com/showthread...hreadid=495854

VBA to open a .mdb file
 

Hi Emoe,

my mistake, try:

Sub OpenLog()
cPATH = "I:\Plant\SHIFTLOG\Shiftlog with Prod.mdb"
Shell "msaccess.exe """ & cPath & """", vbMaximizedFocus
End Sub


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


EMoe wrote :


Hi Keep it cool!

Here is what I set up, but it didn't work.

Sub OpenLog ()
Shell "I:\Plant\SHIFTLOG\Shiftlog with Prod.mdb"
End Sub

Is this right?

EMoe

VBA to open a .mdb file
 

Thanks again.

An error came on the line in yellow. It said that the path could not be
found. I checked the file name for accuracy, and it's correct.

Sub OpenLog()
cPATH = "I:\Plant\SHIFTLOG\Shiftlog with Prod.mdb"
Shell "msaccess.exe """ & cPath & """", vbMaximizedFocus
End Sub

Thanks,
EMoe


--
EMoe
------------------------------------------------------------------------
EMoe's Profile: http://www.excelforum.com/member.php...o&userid=23183
View this thread: http://www.excelforum.com/showthread...hreadid=495854

VBA to open a .mdb file
 

The problem is the spaces in the cPath variable. Try this:


Code
-------------------

Sub OpenLog()
cPath = "I:\Plant\SHIFTLOG\Shiftl~1.mdb"
Shell "msaccess.exe" & " " & cPath, vbMaximizedFocus
End Sub

-------------------


In order to avoid the spaces in the file path, use the old 8dot
format. If there is more than one file in "I:\Plant\SHIFTLOG\" tha
starts with "Shiftl" then you may need to change "Shiftl~1" t
"Shiftl~2" or perhaps a higher number (depending on where it fall
alphabetically ordered in that file).

Also, i think KeepITCool had an extra """ in his statement. I teste
my code with a mdb file on my computer and it worked.

Hope it works for you too

--
Brassma
-----------------------------------------------------------------------
Brassman's Profile: http://www.excelforum.com/member.php...fo&userid=1329
View this thread: http://www.excelforum.com/showthread.php?threadid=49585

VBA to open a .mdb file
 

Thanks Brassman;

I tried a different access file, on the same drive, with a filename
that doesn't have spaces, and I still a *run-time error '53' File not
found*.

The error comes in on the second line of the code (in red)

CODE:
Sub OpenLog()
cPath = "I:\Plant\HR\ODBC.mdb"
Shell "msaccess.exe" & " " & cPath, vbMaximizedFocus
End Sub

Thanks,
EMoe


--
EMoe
------------------------------------------------------------------------
EMoe's Profile: http://www.excelforum.com/member.php...o&userid=23183
View this thread: http://www.excelforum.com/showthread...hreadid=495854

VBA to open a .mdb file
 

How can I open a .mdb file from another computer in the network?


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

VBA to open a .mdb file
 

Hello

I assume you've forgotten quotation-marks around variable named cPath. Try
modify third line of your code for example like below (don't lose space
after "msaccess"):

Shell "msaccess.exe " & chr(34) & cPath & chr(34), vbMaximizedFocus

Good luck
Mariusz


U¿ytkownik "EMoe" napisa³
w wiadomo¶ci ...

Thanks Brassman;

I tried a different access file, on the same drive, with a filename
that doesn't have spaces, and I still a *run-time error '53' File not
found*.

The error comes in on the second line of the code (in red)

CODE:
Sub OpenLog()
cPath = "I:\Plant\HR\ODBC.mdb"
Shell "msaccess.exe" & " " & cPath, vbMaximizedFocus
End Sub

Thanks,
EMoe


--
EMoe
------------------------------------------------------------------------
EMoe's Profile:
http://www.excelforum.com/member.php...o&userid=23183
View this thread: http://www.excelforum.com/showthread...hreadid=495854

VBA to open a .mdb file
 

Maybe check in help and try commands CHDRIVE and CHDIR

Mariusz

U¿ytkownik "stefantem"
napisa³ w
wiadomo¶ci ...

How can I open a .mdb file from another computer in the network?


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

VBA to open a .mdb file
 

Thanks for the reply, I'll look into it.

EMo

--
EMo
-----------------------------------------------------------------------
EMoe's Profile: http://www.excelforum.com/member.php...fo&userid=2318
View this thread: http://www.excelforum.com/showthread.php?threadid=49585

VBA to open a .mdb file
 

I don't want to open the database, but the actual file by its name. This
one is still stumping me???

Bob Phillips Wrote:
Do you want to access the data in the mdb file, or just start the
database
within an Access instance?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"EMoe" wrote in
message
...

Hello programmers!

I want to use vba code to open a Microsoft Access File.

Below is the path. I tried to record the action, but it didn't work.

I:\Documents\SHIFTLOG\Shiftlog with Prod.mdb

Thanks,
EMoe


--
EMoe

------------------------------------------------------------------------
EMoe's Profile:
http://www.excelforum.com/member.php...o&userid=23183
View this thread:
http://www.excelforum.com/showthread...hreadid=495854



--
EMoe
------------------------------------------------------------------------
EMoe's Profile: http://www.excelforum.com/member.php...o&userid=23183
View this thread: http://www.excelforum.com/showthread...hreadid=495854

VBA to open a .mdb file
 

Look at "Shell"

Tim

--
Tim Williams
Palo Alto, CA


"EMoe" wrote in message
...

I don't want to open the database, but the actual file by its name. This
one is still stumping me???

Bob Phillips Wrote:
Do you want to access the data in the mdb file, or just start the
database
within an Access instance?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"EMoe" wrote in
message
...

Hello programmers!

I want to use vba code to open a Microsoft Access File.

Below is the path. I tried to record the action, but it didn't work.

I:\Documents\SHIFTLOG\Shiftlog with Prod.mdb

Thanks,
EMoe


--
EMoe

------------------------------------------------------------------------
EMoe's Profile:
http://www.excelforum.com/member.php...o&userid=23183
View this thread:
http://www.excelforum.com/showthread...hreadid=495854



--
EMoe
------------------------------------------------------------------------
EMoe's Profile:
http://www.excelforum.com/member.php...o&userid=23183
View this thread: http://www.excelforum.com/showthread...hreadid=495854