Beginner: Help on LinkExecute and general Method question.

[elziko]

I want to use the:

object.LinkExecute string

method to send a command to access. However I am writing a function which is in a general module and I dont understand what the object could possible refer to. If I was calling this from an event on a form then the object would be a textbox or something. I dont have any objects because its not on a form, its just a function on its own.

This is my first time with VB so be gentle!

Thanks

elziko

 

[caf]

Hi

You mentioned access is this as is Microsoft Access?
f this is true then instead of using DDE you can use COM. It's much more reliable, faster & powerful than DDE.

I'll be happy to provide you with code if you want (it's not hard in fact it's easier to understand than DDE)

Have fun
caf

BTW: Object refers to Label, PictureBox and Textbox only

If you have your LinkExecute method in a module then you can reference a control via

FormName.ControlName

For instance if you have a textbox called Text1 on Form1 then you can reference it from outside that form whether in a Module, Class, or another form like this

Form1.Text1

 

[elziko]

Thanks caf!

I didnt know you could reference a text box etc off another form. Useful stuff.

Yeah, this COM sounds promising so any advice/code would be useful.

Oh, and I am using Microsoft Access!

Thanks again,

elziko

 

[caf]

Hi

For the example to be efficient you can say what you're trying to do with Access. I'll be able to provide a more specific example.

For now there's little I can do without the information as to what command you'd like to send to Access.

You can practically do anything to Access that you can do from within Access using COM to connect to Access
You can start up Access, Open a db, import/export data to/from excel and close Access with COM
& that's just a simple example of the advantages of using COM to communicate with Access.

You can do much much much more than that though.

 

[elziko]

I wanna send this command (example) to access. I have this in a module but wanna run it from my VB application:

DoCmd.OpenForm "Section38 Current", acNormal, "", "[Section38 Current]![Road Name Rd1]=""Oberon Road""", , acNormal

I have posted a similar question to this but using DDE. I'm just carrying on with DDE at the moment but I will change to COM if its not too hard. Looking forward to see what you come up with and thanks,

elziko

 

[caf]

Hi

Sorry I didn't see your reply until today
Let me just quickly explain how to use Access as a server

You first dimension a variable that's going to hold the reference to MS Access (Think of it as an alias or nickname for MS Access if you must)

I this case any name can be used [It's best to keep it as descriptive as possible]

Dim objAccess As Access.Application

Now If you didn't set a reference to Access then your intellisense would not pick up Access.Application to complete the words

What you have to do is Select Project->References and search for Microsoft Access <version> Object Library.
<I have version 8.0.>

Okay so we've dimensioned the variable of type Access, now we need to activate it (since you aufait with DDE you can think of this as activating the conversation (DDEInitiate)

To activate the link

Set objAccess = GetObject(,&quot;Access.Application&quot

This will return an error if Access is not running
If you really want to comunicate with an active Access session then use GetObject.

If you just want to do some stuff to Access that does not require an active session then use CreateObject
The syntax for CreateObject is similar

CreateObject(&quot;Access.Application&quot

CreateObject will always create a new session of Access. So as with DDE you need to have the Application running so is it with COM. The difference with using COM is that you have more power with the Application

Okay at this point I'll provide the code because if you're not confused by now then you must be familiar with COM.

Option Explicit
Private m_objAccess  As Access.Application

Private Sub Form_Load()

   Const sMDBFile    As String = "d:\db1.mdb"

   Dim bTryAgain     As Boolean
   Dim sCondition    As String
   Dim sFormName     As String

   On Error GoTo ErrorHandler

   bTryAgain = True
   sCondition = "Surname = " & Quote("HENNINGS")
   sFormName = "MyForm"

   Set m_objAccess = GetObject(, "Access.Application")

   If Not m_objAccess Is Nothing Then
      With m_objAccess
         .OpenCurrentDatabase sMDBFile

         DoCmd.OpenForm sFormName, _
                        acNormal, , sCondition, , _
                        acDialog

      End With
   End If

   Exit Sub

ErrorHandler:
   If Err.Number = 429 Then
      If bTryAgain Then
         bTryAgain = False

         If m_objAccess Is Nothing Then
            Set m_objAccess = CreateObject( _
                             "Access.Application" _
                              )
            Resume 'Try using GetObject again
         End If
      End If
   End If

   MsgBox Err.Description, , Err.Number

End Sub

Private Sub Form_QueryUnload( _
   Cancel As Integer, UnloadMode As Integer _
   )

   If Not m_objAccess Is Nothing Then
      m_objAccess.Quit
      Set m_objAccess = Nothing
   End If

End Sub
Private Function Quote(sString As String) As String

   Quote = Chr(34) & sString & Chr(34)

End Function

Don't feel daunted by the amount of code (I always tend to get out of hand) It's the Error Handling that takes up so much space.
The code could easily have been...

Dim x As New Access.Application
x.DoCmd.OpenForm "Section38 Current", acNormal, "", "[Section38 Current]![Road Name Rd1]=""Oberon Road""", , acNormal

But I hate sloppy code #-)

Have fun
caf