Results 1 to 13 of 13

Thread: [RESOLVED] Error while executing a VBA Code to get MS Access database table field details ...

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2014
    Posts
    326

    Resolved [RESOLVED] Error while executing a VBA Code to get MS Access database table field details ...

    I got this code from searching on Google to get MS Access database table field details to excel. But getting the below error:
    ---------------------------
    Microsoft Visual Basic for Applications
    ---------------------------
    Compile error:

    Expected user-defined type, not project
    ---------------------------
    OK Help
    ---------------------------


    The following is the code written in Module:
    Code:
    Option Compare Database
    Option Explicit
     
    Sub ListTablesAndFields()
         'Macro Purpose:  Write all table and field names to and Excel file
         
        Dim lTbl As Long
        Dim lFld As Long
        Dim dBase As Database
        Dim xlApp As Object
        Dim wbExcel As Object
        Dim lRow As Long
         
         'Set current database to a variable adn create a new Excel instance
        Set dBase = CurrentDb
        Set xlApp = CreateObject("Excel.Application")
        Set wbExcel = xlApp.workbooks.Add
         
         'Set on error in case there is no tables
        On Error Resume Next
         
         'Loop through all tables
        For lTbl = 0 To dBase.TableDefs.Count
             'If the table name is a temporary or system table then ignore it
            If Left(dBase.TableDefs(lTbl).Name, 1) = "~" Or _
            Left(dBase.TableDefs(lTbl).Name, 4) = "MSYS" Then
                 '~ indicates a temporary table
                 'MSYS indicates a system level table
            Else
                 'Otherwise, loop through each table, writing the table and field names
                 'to the Excel file
                For lFld = 1 To dBase.TableDefs(lTbl).Fields.Count - 1
                    lRow = lRow + 1
                    With wbExcel.sheets(1)
                        .Range("A" & lRow) = dBase.TableDefs(lTbl).Name
                        .Range("B" & lRow) = dBase.TableDefs(lTbl).Fields(lFld).Name
                    End With
                Next lFld
            End If
        Next lTbl
         'Resume error breaks
        On Error GoTo 0
         
         'Set Excel to visible and release it from memory
        xlApp.Visible = True
        Set xlApp = Nothing
        Set wbExcel = Nothing
         
         'Release database object from memory
        Set dBase = Nothing
         
    End Sub
    The attached image shows the references selected :
    Attached Images Attached Images  

  2. #2
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,084

    Re: Error while executing a VBA Code to get MS Access database table field details ..

    Guess what i did:
    i copied your ListTablesAndFields sub
    and pasted it in an access 2003 module with the Northwind.mdb (access 2000 file format)
    and it did exactly what it is supposed to do
    do not put off till tomorrow what you can put off forever

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2014
    Posts
    326

    Re: Error while executing a VBA Code to get MS Access database table field details ..

    I am using MS Access 2013 Database (Test.accdb). Do I need to mention database name in that code?

    Note: My Database is password protected.
    Last edited by VS2013; Jan 25th, 2018 at 04:19 PM.

  4. #4

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2014
    Posts
    326

    Re: Error while executing a VBA Code to get MS Access database table field details ..

    I tried but getting the same error. May be becuase of File Format it throws that error. Or any refence file is missing.

  5. #5
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,084

    Re: Error while executing a VBA Code to get MS Access database table field details ..

    maybe you could say where in the code that error is occuring
    also, i just noticed : Dim dBase As Database
    'Database' is a DAO object
    yet i do not see any reference to DAO

    but i do see a reference to Microsoft Office 15.0 Access database engine Object
    what i think is a reference to ACEDAO (i can not be sure of that,since i do not have access 2013)

    so just try putting in a reference to DAO, and giving it a high priorety (higher then ADO)
    do not put off till tomorrow what you can put off forever

  6. #6
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,343

    Re: Error while executing a VBA Code to get MS Access database table field details ..

    As the example doesnt have a password, try taking off the password. See if it affects it
    If it does, then you will probably need to amend the open database line. Shouldnt affect it though.

    You dont need ADO if you are already in Access (unless you need it elsewhere?)

    Did the error show a particular line ?

    Not sure you need powerpoint, outlook, office... depends what you are doing

    Excel reference isnt really required as you can use:
    Code:
    dim objXL as object
    
    set objXL = createObject("excel.application")
    sort of thing, but as its a first time trying it, leave it in.

    Just be aware that vba sometimes gets confused when there are functions with the same name if you arent explicitly declaring them when using...

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

  7. #7
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,084

    Re: Error while executing a VBA Code to get MS Access database table field details ..

    Guess what happened today
    the granddaughter that has access 2010 on her laptop came by today
    so,i copied your ListTablesAndFields sub
    and pasted it in an access 2010 module with the NWIND.ACCDB database
    and it did exactly what it is supposed to do

    so,i am willing to bet a box of chocolates it will work on any version of access
    (at least from 2000 on)
    do not put off till tomorrow what you can put off forever

  8. #8
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Error while executing a VBA Code to get MS Access database table field details ..

    Quote Originally Posted by VS2013 View Post
    The following is the code written in Module:
    What you didn't mention was the name of the Module you created...I just wonder if you called it "Database" by chance.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  9. #9
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Error while executing a VBA Code to get MS Access database table field details ..

    Hi,

    with a Name like VS2013, why not use VS

    Code:
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=D:\Northwind.mdb")
            Dim Tablename As String = "Orders"
            con.Open()
            Dim DT As DataTable = con.GetSchema("Tables")
            Dim Cmd As OleDbCommand = con.CreateCommand()
            Dim dr As OleDbDataReader
    
            With Cmd
                .CommandText = "Select * From " & Tablename
                .CommandType = CommandType.Text
                dr = .ExecuteReader
            End With
            DT = dr.GetSchemaTable
            con.Close()
            DataGridView1.DataSource = DT
        End Sub
    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  10. #10

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2014
    Posts
    326

    Re: Error while executing a VBA Code to get MS Access database table field details ..

    Thanks for the support.

  11. #11
    New Member
    Join Date
    Feb 2018
    Posts
    1

    Re: [RESOLVED] Error while executing a VBA Code to get MS Access database table field

    Thanks all for your support and get me out for this.




    Vegetarian Foods
    Last edited by rabiyajamal45; Oct 23rd, 2023 at 04:09 AM.

  12. #12
    Lively Member
    Join Date
    Feb 2017
    Posts
    105

    Re: Error while executing a VBA Code to get MS Access database table field details ..

    Quote Originally Posted by ChrisE View Post
    Hi,

    with a Name like VS2013, why not use VS

    Code:
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=D:\Northwind.mdb")
            Dim Tablename As String = "Orders"
            con.Open()
            Dim DT As DataTable = con.GetSchema("Tables")
            Dim Cmd As OleDbCommand = con.CreateCommand()
            Dim dr As OleDbDataReader
    
            With Cmd
                .CommandText = "Select * From " & Tablename
                .CommandType = CommandType.Text
                dr = .ExecuteReader
            End With
            DT = dr.GetSchemaTable
            con.Close()
            DataGridView1.DataSource = DT
        End Sub
    regards
    Chris
    hi, i just copied this and pasted it in visual basic 2015
    got the following errors:
    Type 'OleDbCommand' is not defined
    Type 'OleDbConnection is not defined
    Type 'OleDbConnection is not defined
    Type 'OleDbDataReader' is not defined

    what's going on ? what am i missing

  13. #13
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: [RESOLVED] Error while executing a VBA Code to get MS Access database table field

    Hi,

    add this to your Form...
    Code:
    Option Strict On
    Imports System.Data.OleDb
    
    
    Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=D:\Northwind.mdb")
            Dim Tablename As String = "Orders"
            con.Open()
            Dim DT As DataTable = con.GetSchema("Tables")
            Dim Cmd As OleDbCommand = con.CreateCommand()
            Dim dr As OleDbDataReader
    
            With Cmd
                .CommandText = "Select * From " & Tablename
                .CommandType = CommandType.Text
                dr = .ExecuteReader
            End With
            DT = dr.GetSchemaTable
            con.Close()
            DataGridView1.DataSource = DT
        End Sub
    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width