none
Questions regarding Access Interop libraries RRS feed

  • Question

  • Hello,

    I do hope I am in the right forum regarding some of those topics.

    I want to know how to perform the following step.

    How to invoke a DLookup function while using the Access Interop library.

    For the past several years, I have viewed over various advanced tutorials in Access VBA using those functions.

    So far, none of them have been implemented into Vb.net forms.

    In addition, I would like to learn more on how to invoke Record sets while using the same related library.

    Regards,

    JohnDBCTX


    jp

    Sunday, November 11, 2018 5:15 AM

Answers

  • I found some solutions:

    An application interface, which includes a DLookup method, is not needed.

    Imports Microsoft.Office.Interop.Access.Dao
    Imports Microsoft.SqlServer.Server
    Imports Microsoft.Office.Interop
    Imports Microsoft
    
    
    Public Class Form1
        Private DB As Database
        Private RST As Recordset
        Private DBEng As New DBEngine
    
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    
            DB = DBEng(0).OpenDatabase("C:\Users\johnd\Documents\NamesDBThree.mdb")
            'Instantiate DBEngine to access the datbase file.
            RST = DB.OpenRecordset("tblNames")
            'Instantiate Recordset to access the tblNames table.
    
    
        End Sub
    
        Private Sub FirstRecordButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FirstRecordButton.Click
            RecordsetThisDataBase()
            RST = DB.OpenRecordset("tblNames")
            NameIDPrimaryKeyTextBox.Text = RST(0).Value
            LastNameTextBox.Text = RST(1).Value
            FirstNameTextBox.Text = RST(2).Value
    
            RST.MoveFirst()
    
        End Sub
    
        Public Sub RecordsetThisDataBase()
            SpaceTextBox.Text = ""
    
            Do Until RST.EOF
                SpaceTextBox.Text &= RST(0).Value & ", " & RST(1).Value & ", " & RST(2).Value & vbNewLine
                RST.MoveNext()
            Loop
            RST.Close()
    
    
        End Sub
    
     
    
        Private Sub PreviousRecordButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PreviousRecordButton.Click
            RST.MovePrevious()
    
            NameIDPrimaryKeyTextBox.Text = RST(0).Value
            LastNameTextBox.Text = RST(1).Value
            FirstNameTextBox.Text = RST(2).Value
    
            
    
        End Sub
    
        Private Sub NextRecordButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NextRecordButton.Click
            RST.MoveNext()
            NameIDPrimaryKeyTextBox.Text = RST(0).Value
            LastNameTextBox.Text = RST(1).Value
            FirstNameTextBox.Text = RST(2).Value
    
    
    
        End Sub
    
        Private Sub LastRecordButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LastRecordButton.Click
            RST.MoveLast()
            NameIDPrimaryKeyTextBox.Text = RST(0).Value
            LastNameTextBox.Text = RST(1).Value
            FirstNameTextBox.Text = RST(2).Value
    
    
        End Sub
    
        Private Sub EmailAddressesFormButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles EmailAddressesFormButton.Click
            EMail_Addresses.Show()
    
        End Sub
    End Class
    
    
    
    
    Imports Microsoft.Office.Interop.Access.Dao
    Imports Microsoft.SqlServer.Server
    Imports Microsoft.Office.Interop
    Imports Microsoft
    Public Class EMail_Addresses
        Private DB As Database
        Private RSTTwo As Recordset
        Private DBEng As New DBEngine
        Private strSQL As String
    
    
        Private Sub EMail_Addresses_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            strSQL = "SELECT tblEMailAddresses.InfoID,  tblEMailAddresses.EMailAddress, tblEMailAddresses.NameID   FROM tblEMailAddresses, tblNames WHERE tblEMailAddresses.NameID=" & Form1.NameIDPrimaryKeyTextBox.Text
    
    
    
            DB = DBEng(0).OpenDatabase("C:\Users\johnd\Documents\NamesDBThree.mdb")
    
    
            RSTTwo = DB.OpenRecordset(strSQL)
    
            InfoIDPrimaryKeyTextBox.Text = RSTTwo(0).Value
            EMailAddressTextBox.Text = RSTTwo(1).Value
            NameIDForeignKeyTextBox.Text = RSTTwo(2).Value
    
        End Sub
    End Class
    
    
    

    I compiled it and finally it ran perfectly. 

    The way some developers would prefer while performing database techniques and operations, fewer controls would give them more productivity.  In other words, if developers would place fewer controls, and implement fewer lines of code, then their computer systems should use less of its memory and its space.  Agreed?

    Regards,

    JohnDBCTX


    jp

    • Marked as answer by JohnDBCTX Wednesday, November 14, 2018 8:36 AM
    Wednesday, November 14, 2018 8:36 AM

All replies

  • Imports Microsoft.Office.Interop.Access
    
    Public Class Form1
        Private DBX As DAO.Database
        Private AccWz As Application
        Private RecSet As DAO.Recordset
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            AccWz = New Application
            AccWz.OpenCurrentDatabase("C:\Users\johnd\Documents\NamesDBTwo.mdb")
    
            DBX = AccWz.CurrentDb
            RecSet = DBX.OpenRecordset("tblNames")
    
            NameIDTextBox.Text = RecSet.Fields(0).Value
            LastNameTextBox.Text = RecSet.Fields(1).Value
            FirstNameTextBox.Text = RecSet.Fields(2).Value
    
    
        End Sub
    End Class

    Maybe this code snippet would help. It compiles and runs perfectly.  This can be a step towards linking one form to another form using a one-to-many relationship while using the DLookup function in a foreign key text box in a second form.

    Regards,

    JohnDBCTX


    jp

    • Marked as answer by JohnDBCTX Tuesday, November 13, 2018 1:35 AM
    • Unmarked as answer by JohnDBCTX Tuesday, November 13, 2018 3:18 AM
    Sunday, November 11, 2018 10:50 AM
  • Imports Microsoft.Office.Interop.Access
    
    Public Class Form2
        Private DBX As DAO.Database
        Private AccWz As Application
        Private RecSet As DAO.Recordset
        Private Sub Form2_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            AccWz = New Application
            AccWz.OpenCurrentDatabase("C:\Users\johnd\Documents\NamesDBTwo.mdb")
    
            DBX = AccWz.CurrentDb
            RecSet = DBX.OpenRecordset("tblEmailAddresses")
    
            InfoIDPrimaryKeyTextBox.Text = RecSet.Fields(0).Value
            EMailAddressTextBoxSpace.Text = RecSet.Fields(1).Value
            NameIDForeignKeyTextBox.Text = AccWz.DLookup("[NameID]", "tblEmailAddresses", "'[NameID]=[tblNames].[NameID]'")
    
        End Sub
    End Class

    The second half of this code snippet compiles and runs perfectly as well.  

    Note that I these two code snippets require one form each.

    Regards,

    JohnDBCTX


    jp

    • Marked as answer by JohnDBCTX Tuesday, November 13, 2018 1:35 AM
    • Unmarked as answer by JohnDBCTX Tuesday, November 13, 2018 3:18 AM
    Sunday, November 11, 2018 12:13 PM
  • Hi jp,

    Did you solve your problem?
    If so, you can mark it as answer and please help us close the thread. 

    Best Regards,

    Yuki


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Monday, November 12, 2018 9:09 AM
    Moderator
  • I may have solved my problem.  It worked for a little while. 

    But now I got this error message.

    System.Runtime.InteropServices.COMException
      HResult=0x80040154
      Message=Retrieving the COM class factory for component with CLSID {73A4C9C1-D68D-11D0-98BF-00A0C90DC8D9} failed due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)).
      Source=mscorlib
      StackTrace:
       at System.RuntimeTypeHandle.CreateInstance(RuntimeType type, Boolean publicOnly, Boolean noCheck, Boolean& canBeCached, RuntimeMethodHandleInternal& ctor, Boolean& bNeedSecurityCheck)
       at System.RuntimeType.CreateInstanceSlow(Boolean publicOnly, Boolean skipCheckThis, Boolean fillCache, StackCrawlMark& stackMark)
       at System.RuntimeType.CreateInstanceDefaultCtor(Boolean publicOnly, Boolean skipCheckThis, Boolean fillCache, StackCrawlMark& stackMark)
       at System.Activator.CreateInstance(Type type, Boolean nonPublic)
       at System.Activator.CreateInstance(Type type)
       at List_Of_Names_Build_01.Form1.Form1_Load(Object sender, EventArgs e) in C:\Users\johnd\source\repos\List Of Names Build 01\List Of Names Build 01\Form1.vb:line 29
       at System.EventHandler.Invoke(Object sender, EventArgs e)
       at System.Windows.Forms.Form.OnLoad(EventArgs e)
       at System.Windows.Forms.Form.OnCreateControl()
       at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
       at System.Windows.Forms.Control.CreateControl()
       at System.Windows.Forms.Control.WmShowWindow(Message& m)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
       at System.Windows.Forms.Form.WmShowWindow(Message& m)
       at System.Windows.Forms.Form.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

    I have to unmark those answers due to that matter.  Can anyone fix this problem?

    Regards,

    JohnDBCTX


    jp

    Tuesday, November 13, 2018 3:19 AM
  •     Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            strFileX="C:\Users\johnd\Documents\NamesDBThree.mdb"
            AZap = New Application     'The following missing class or dynamic link library could be missing.
    
            AZap.OpenCurrentDatabase(strFileX)
            AZap = AZap.CurrentDb
    
    
            DBX = DBX.OpenTable("tblNames").OpenRecordset
    
            NameIDTextBox.Text = RecSet.Fields(0).Value
            LastNameTextBox.Text = RecSet.Fields(1).Value
            FirstNameTextBox.Text = RecSet.Fields(2).Value
    
        End Sub
    

    Perhaps what I may be missing is how I could be opening a database file, and how to bring its data to its unbound text box controls.  That is the only obstacle that stands in its way. 

    Regards,

    JohnDBCTX

     

    jp

    Tuesday, November 13, 2018 3:26 AM
  • Well, numerous times I have tried to compile it. 

    Still, I get the following error message number: 80040154

    A System.Runtime.InteropServices.COMException error.

    Maybe I need to do more research on that error message number. 

    Regards,

    JohnDBCTX


    jp

    Tuesday, November 13, 2018 10:40 AM
  • I found some solutions:

    An application interface, which includes a DLookup method, is not needed.

    Imports Microsoft.Office.Interop.Access.Dao
    Imports Microsoft.SqlServer.Server
    Imports Microsoft.Office.Interop
    Imports Microsoft
    
    
    Public Class Form1
        Private DB As Database
        Private RST As Recordset
        Private DBEng As New DBEngine
    
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    
            DB = DBEng(0).OpenDatabase("C:\Users\johnd\Documents\NamesDBThree.mdb")
            'Instantiate DBEngine to access the datbase file.
            RST = DB.OpenRecordset("tblNames")
            'Instantiate Recordset to access the tblNames table.
    
    
        End Sub
    
        Private Sub FirstRecordButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FirstRecordButton.Click
            RecordsetThisDataBase()
            RST = DB.OpenRecordset("tblNames")
            NameIDPrimaryKeyTextBox.Text = RST(0).Value
            LastNameTextBox.Text = RST(1).Value
            FirstNameTextBox.Text = RST(2).Value
    
            RST.MoveFirst()
    
        End Sub
    
        Public Sub RecordsetThisDataBase()
            SpaceTextBox.Text = ""
    
            Do Until RST.EOF
                SpaceTextBox.Text &= RST(0).Value & ", " & RST(1).Value & ", " & RST(2).Value & vbNewLine
                RST.MoveNext()
            Loop
            RST.Close()
    
    
        End Sub
    
     
    
        Private Sub PreviousRecordButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PreviousRecordButton.Click
            RST.MovePrevious()
    
            NameIDPrimaryKeyTextBox.Text = RST(0).Value
            LastNameTextBox.Text = RST(1).Value
            FirstNameTextBox.Text = RST(2).Value
    
            
    
        End Sub
    
        Private Sub NextRecordButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NextRecordButton.Click
            RST.MoveNext()
            NameIDPrimaryKeyTextBox.Text = RST(0).Value
            LastNameTextBox.Text = RST(1).Value
            FirstNameTextBox.Text = RST(2).Value
    
    
    
        End Sub
    
        Private Sub LastRecordButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LastRecordButton.Click
            RST.MoveLast()
            NameIDPrimaryKeyTextBox.Text = RST(0).Value
            LastNameTextBox.Text = RST(1).Value
            FirstNameTextBox.Text = RST(2).Value
    
    
        End Sub
    
        Private Sub EmailAddressesFormButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles EmailAddressesFormButton.Click
            EMail_Addresses.Show()
    
        End Sub
    End Class
    
    
    
    
    Imports Microsoft.Office.Interop.Access.Dao
    Imports Microsoft.SqlServer.Server
    Imports Microsoft.Office.Interop
    Imports Microsoft
    Public Class EMail_Addresses
        Private DB As Database
        Private RSTTwo As Recordset
        Private DBEng As New DBEngine
        Private strSQL As String
    
    
        Private Sub EMail_Addresses_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            strSQL = "SELECT tblEMailAddresses.InfoID,  tblEMailAddresses.EMailAddress, tblEMailAddresses.NameID   FROM tblEMailAddresses, tblNames WHERE tblEMailAddresses.NameID=" & Form1.NameIDPrimaryKeyTextBox.Text
    
    
    
            DB = DBEng(0).OpenDatabase("C:\Users\johnd\Documents\NamesDBThree.mdb")
    
    
            RSTTwo = DB.OpenRecordset(strSQL)
    
            InfoIDPrimaryKeyTextBox.Text = RSTTwo(0).Value
            EMailAddressTextBox.Text = RSTTwo(1).Value
            NameIDForeignKeyTextBox.Text = RSTTwo(2).Value
    
        End Sub
    End Class
    
    
    

    I compiled it and finally it ran perfectly. 

    The way some developers would prefer while performing database techniques and operations, fewer controls would give them more productivity.  In other words, if developers would place fewer controls, and implement fewer lines of code, then their computer systems should use less of its memory and its space.  Agreed?

    Regards,

    JohnDBCTX


    jp

    • Marked as answer by JohnDBCTX Wednesday, November 14, 2018 8:36 AM
    Wednesday, November 14, 2018 8:36 AM