Results 1 to 5 of 5
  1. #1
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116

    VBA code is not working

    Hi,



    I started copying out some code from a Youtube clip I found, and have pasted the first part out below. For some reason it's not working for me, and I can't work out why. Here's the code.

    Sub GetDataFromAccess()


    Dim DBFullName As String
    Dim Connect As String, Source As String
    Dim Connection As ADODB.Connection
    Dim Recordset As ADODB.Recordset
    Dim Col As Integer


    DBFullName = "D:\Documents\Orchestra\Musicians Details\Orchestra.accdb"


    Set Connection = New ADODB.Connection
    Connect = "Provider=Microsoft.ACE.OLEDB.12.0;"
    Connect = Connect & "Data Source=" & DBFullName & ";"
    Connection.Open ConnectionString = Connect


    End Sub

    It is the very last line that causes the problem. It brings up an error messsage Run-time error '-2147467259 (80004005)': Automation error, Unspecified error.

    In case it's relevant, here is a list of all the references that I have ticked. Am I missing anything, or are there any superfluous references there?

    Visual Basic for Applications
    Microsoft Excel 12.0 Object Library
    OLE Automation
    Microsoft Office 12.0 Object Library
    Microsoft Forms 2.0 Object Library
    Microsoft Outlook 12.0 Object Library
    Microsoft Access 12.0 Object Library
    Microsoft Scripting Runtime
    Microsoft ActiveX Data Objects 6.1 Library
    Microsoft Office 12.0 Access Database Engine Object Library

    I've searched on Google for the problem. Could it relate to the fact that I'm using a 32 bit version of Microsoft Office. Does this code work for others?

    In case you're wondering, the rest of the code is to grab data from an Access table, and enter it in an Excel workbook.

    Many thanks for your help.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you can attach the tables directly and eliminate all this code.

  3. #3
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    Thanks. How do I go about attaching an Access table to Excel? If I update the table, will it automatically update the attachment?

  4. #4
    sgroenen is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Location
    Hamont-Achel
    Posts
    6
    I give you code how it would work in my acces
    Dim sspcnn As ADODB.Connection
    Dim sspcmd As ADODB.Command
    Dim ssprst As ADODB.Recordset
    Private Sub Form_Open(Cancel As Integer)

    'leegmaken van de bestringselementen
    ' vergrendelen van de besturingselementen
    ' opdrachtknoppen in- of uitschakelen
    leegmaken
    vergrendelen
    ' Openen verbinding
    Set sspcnn = New ADODB.Connection
    sspcnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=R:\ssp.accdb"
    'Command objecten creeeren
    Set sspcmd = New ADODB.Command
    sspcmd.ActiveConnection = sspcnn
    sspcmd.CommandType = adCmdStoredProc
    sspcmd.CommandText = "qrysspFM"
    'opdracht uitvoeren en recordset maken
    sspcmd.Execute
    Set ssprst = New ADODB.Recordset
    ssprst.Open sspcmd
    'opvullen van de besturingselementen
    txtBestandsnaam = ssprst.Fields("Bestandsnaam").Value
    txtOmschrijving = ssprst.Fields("Omschrijving").Value
    txtNieuweversie = ssprst.Fields("VersieSSP").Value
    txtHuidigeversie = ssprst.Fields("OudeVersie").Value
    txtDatum = ssprst.Fields("Datum").Value
    End Sub
    this is for me already a start, no error when open. Just copy past and change what you need. I have this from a good book so this is working in access 2016 and normal also in your version.
    Hope this give you the answer

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    [QUOTE=neilsolaris;459385] I started copying out some code from a Youtube clip I found, and have pasted the first part out below. For some reason it's not working for me, and I can't work out why. Here's the code.

    <snip>

    In case you're wondering, the rest of the code is to grab data from an Access table, and enter it in an Excel workbook.
    [/Code]
    Maybe give more info about what you are trying to do??
    Are you in Access and want to copy data from an external dB into Excel?? Not sure what is the goal...

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 28
    Last Post: 01-24-2018, 05:14 PM
  2. Replies: 2
    Last Post: 05-14-2017, 10:07 AM
  3. code not working
    By jj1 in forum Access
    Replies: 4
    Last Post: 03-17-2015, 10:33 AM
  4. VB Code not working
    By gykiang in forum Forms
    Replies: 1
    Last Post: 03-08-2015, 10:52 AM
  5. Replies: 2
    Last Post: 06-28-2013, 12:58 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums