vb to call a userform in an access database?

Can I ask why you are doing this?

Is there data in the Access database that you want?

If so I'm sure there's a better way to get it than opening up the database.

Have you thought of using Data>Get External Data?

Hi Norie and others,

Actually the reason why I'm wanting to do it this way is that its just simply like an application, i've got a excel modelling application based on Excel, but there is a substantial amount of information that doesn't need to be inserted into Excel, its not about inserting or incorporating Access information at all. The access database which has already userforms that are search forms basically are for trawling this database of intelligence information. Its just a reference library.

I thought it would be good that there is a command button or opportunity for the user of the excel model to call up the relevant form and the database if need be to look up things. Nothing gets incorporated from Access to Excel.

It makes it a more cohensive looking application, otherwise user must go to directory, locate the database and do a search and than come back..

hence, all i need is the code to call the database and the relevant form within this database from my excel button.

thanks again.

Acouple ideas for doing it, both with draw backs
using automatio there is the problem that excel closes access as soon as the variable goes out of range. it works Ok though as long as you open the form in Dialog mode.
I prefer to use hyperlinks, this will not open a form but you can set access to open to a form by default or if there are several form to choose from then use a switch board.

Peter

Sub getAccess()
Dim appAccess As Object
Set appAccess = GetObject("C:\db1.mdb")
appAccess.Visible = True
appAccess.DoCmd.OpenForm "form1", , , , , acDialog
End Sub

Sub GetAccess2()
ActiveWorkbook.FollowHyperlink Address:="C:\db1.mdb", NewWindow:=True
End Sub

What do you mean by the expressin:

"using automatio there is the problem that excel closes access as soon as the variable goes out of range."

i'm not proficient so please explain anyone..

ok you've given me two options, the reason why i'd like to open a form from the access is tha tthe form isbasically a search form,

I'll give it a try.

thanks everyone.

Just curious..

in the code.. what does this mean.. and why so many ", , , , , "

appAccess.DoCmd.OpenForm "form1", , , , , acDialog


and what does it mean?

the appAccess is just telling excel who the next bit belongs to

all the commas are to fill in the blanks when you dont use an argument.
From Access help is the full syntax for the openForm
DoCmd.OpenForm formname[, view][, filtername][, wherecondition][, datamode][, windowmode][, openargs]

The bit about Access closing is that Excel will close Access when the code stops running, if you open the Access form in Dialog mode then code is stopped until access is closed. without Dialog you will probably just get a flicker

Peter

Hi Bat17,

Thanks for your assistance.

I've got one last question. I've tried the code that you've suggested, both of them, and they work fine.

However, I'm no good with calling directories or path names when its not the plain vanilla example you've provided.

For example, moreover, its with respect to the path that i am faced with at work.

For example, for our shared drive, witin My Computer, the drive is described as follows:

sbb&c_cat on 'National Share Server (ntidshare)' (z

so I've worked out how to for example create a hyperlink when emailing people and it looks like the following:

<<file://ntidshare\sbb&c_cat\admin\adminphone.doc>>

This shows an example of how the hyperlinking works on our system,

but for the purpose of linking with the code that you've provided, what could this look like?

How would i change your code below:
C:\db1.mdb

to reflect my work environment?

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

i've got another drive that is my own at work, which has the description from My Computer of:

ugffy $ on 'Mel02239' (H

where the term: ugffy is my username for our systems and
the server name is 'Mel02239' and is commonly referred to as my H drive.

thanks for your assistance..

At a guess it will be "\\Mel02239\ugffy\db1.mdb" for your drive and "\\'National Share Server (ntidshare)\sbb&c_cat\db1.mdb" for your first drive, though I am not sure what that "&" in the middle of it will do to it!
Unfortunately your hyperlink did not show up or that might have given us a clue