Consulting

Results 1 to 19 of 19

Thread: Solved: Create access database from excel?

  1. #1
    VBAX Expert
    Joined
    Jan 2005
    Posts
    574
    Location

    Solved: Create access database from excel?

    Is it possible to create an access database and tables from within excel and then export my data into the database i just created?

    Any ideas or links for where to look?

    Thanks

    Gibbo

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    You can easily do it using ADO/ADOX. There is an MSDN article

    http://msdn.microsoft.com/library/de...cesstables.asp
    Creating and Modifying Access Tables
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Expert
    Joined
    Jan 2005
    Posts
    574
    Location
    Thanks, I need to go a step back first though and ask how i can create a new access database in the first pace

    Thanks

    Gibbo

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by gibbo1715
    Thanks, I need to go a step back first though and ask how i can create a new access database in the first pace

    Thanks

    Gibbo
    Even simpler

    [VBA]
    Option Explicit

    #Const EarlyBound = False 'if using early binding

    '----------------------------------------------------------------
    Sub CreateAccessDatabase(DBPath As String)
    '----------------------------------------------------------------
    #If EarlyBound Then
    Dim oADOCat As ADOX.Catalog

    Set oADOCat = New ADOX.Catalog
    #Else
    Dim oADOCat As Object

    Set oADOCat = CreateObject("ADOX.Catalog")
    #End If

    oADOCat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & DBPath

    Set oADOCat = Nothing
    End Sub
    [/VBA]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Expert
    Joined
    Jan 2005
    Posts
    574
    Location
    thanks xld

    but i get an error authentication failed

    I have access 2000 installed on my computer

    Cheers

    Gibbo

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by gibbo1715
    thanks xld

    but i get an error authentication failed

    I have access 2000 installed on my computer

    Cheers

    Gibbo
    So do I have Access 2000. Where is the error? Are you early or late binding? Do you have permission to write to the folder?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    VBAX Expert
    Joined
    Jan 2005
    Posts
    574
    Location
    So do I have Access 2000. Where is the error? Are you early or late binding? Do you have permission to write to the folder?
    I am running this on a standalone machine and I have full access to all of it.

    Early or late binding? you will have to explain the difference

    I am running the code from a button within excel and was trying the following

    [VBA] Sub Button1_Click()
    #If EarlyBound Then
    Dim oADOCat As ADOX.Catalog

    DBPath = "C:\Test.mdb"

    Set oADOCat = New ADOX.Catalog
    #Else
    Dim oADOCat As Object

    Set oADOCat = CreateObject("ADOX.Catalog")
    #End If

    oADOCat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & DBPath

    Set oADOCat = Nothing
    End Sub
    [/VBA]

    I confess to being an absolute beginner at this Access/ Excel linking

    Thanks in advance for being so patient with me

    Gibbo

  8. #8
    VBAX Expert
    Joined
    Jan 2005
    Posts
    574
    Location
    Real Sorry XLD, Having just looked at it on the forum see my obvious mistake

    [VBA] Sub Button1_Click()

    DBPath = "C:\Test.mdb" ' <<<<<<<<<Moved
    #If EarlyBound Then
    Dim oADOCat As ADOX.Catalog


    Set oADOCat = New ADOX.Catalog
    #Else
    Dim oADOCat As Object

    Set oADOCat = CreateObject("ADOX.Catalog")
    #End If

    oADOCat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & DBPath

    Set oADOCat = Nothing
    End Sub[/VBA]

    This now works, sorry for waisting your time,

    I do feel like an idiot

    Gibbo

  9. #9
    VBAX Expert
    Joined
    Jan 2005
    Posts
    574
    Location
    Not Quite there Yet

    Can you tell me why i get arguments of the wrong type as below when i try to add records to my table?

    [VBA]
    Dim oADOCat As Object
    Dim tblNew As Object

    DBPath = "C:\Test.mdb"

    Set oADOCat = CreateObject("ADOX.Catalog")
    Set tblNew = CreateObject("ADOX.Table")

    'Open the catalog.
    oADOCat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & DBPath
    ' Create a new Table object.
    With tblNew
    .Name = "Contacts"
    ' Create fields and append them to the
    ' Columns collection of the new Table object.
    With .Columns
    .Append "FirstName", adVarWChar '<<<Error Here
    .Append "LastName", adVarWChar
    .Append "Phone", adVarWChar
    .Append "Notes", adLongVarWChar
    End With
    End With

    ' Add the new Table to the Tables collection of the database.
    oADOCat.Tables.Append tblNew

    Set oADOCat = Nothing [/VBA]

    Thanks

    Gibbo

  10. #10
    VBAX Mentor XL-Dennis's Avatar
    Joined
    May 2004
    Location
    ?stersund, Sweden
    Posts
    499
    Location
    Gibbo,

    Use adWChar as (if I remember it right the other types are not supported in MDBs) and specify the max length:

    [vba]
    .Append "FirstName", adWChar, 10
    [/vba]

    You may also take a look here:
    http://www.ozgrid.com/forum/showthread.php?t=40365


    Kind regards,
    Dennis
    Kind regards,
    Dennis

    ExcelKB | .NET & Excel | 2nd edition PED


  11. #11
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    I have attached a small csv file which indicates the ADO types and appropriate Access Field types - with emphasis on differentiating between Access 97 and 2000+. You may find it a useful reference.

    Stan

  12. #12
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    A tutorial

    Binding is a process of matching function calls to the actual code (internal or external) that implements the function. It is done when the application is compiled or when it runs, but functions called in code must be bound before the code can be executed.

    When using early binding, a reference to the object class' type library has to be set in the VBIDE (Tools>References), so that the information is gathered prior to compiling. By having this information available to the compiler, it is more efficient at run time. Late binding does not need a reference set, but gets the information at run time, which requires repeated calls to the type library to retrieve the information. Early binding can give problems where an application is developed under one version of the product, but deployed to an earlier version, as the library is not found. For this reason, the code should always be developed under the earliest version of the product that it will be deployed to, or failing that, using late binding.

    What determines whether you are using early or late binding is the variable declaration. If the variable is declared as a type 'Object' it is late bound. If declared as a specific object type it is early bound. How the object is instantiated has no effect on binding. That is, instantiating with '= New Class' or with '= CreateObject(Prog.ID)' does not determine the binding method, it is the variable object declaration. The New keyword instructs the compiler to directly access the object's type library to get information about that object. CreateObject instructs the compiler to look in the registry for the object and locate the type library based on that info.

    There is never any reason to use CreateObject rather than New when you are early binding. Doing so just adds an unnecessary detour through the registry. Conversely, is not possible to use New when instantiating the object if the variable was declared as type 'Object, i.e. late binding.

    As a slight aside, the standard way to instantiate say an Outlook object using early binding would be

    Dim olApp As Outlook.Application

    Set olApp = New Outlook.Application

    However, it is possible to do that in one step with

    Dim olApp As New Outlook.Application

    This is not creating a new instance of an Outlook object in the Dim statement, but rather is telling the compiler to ensure that the Outlook object exists whenever referenced. Variables declared in this way are called "auto-instancing" variables.

    What the compiler effectively does is create code like

    Dim olApp As Outlook.Application
    ' more of your code
    If olApp Is Nothing Then
    Set olApp = New Oulook.Application
    End If

    The compiler does this before every reference to the variable olApp Whilst the test for "Is Nothing" is not a huge overhead, it can add up in long loops in big applications. Worse though, it prevents testing for "Is Nothing" in the code itself as simply using the variable olApp in code ensures that it is instantiated, and thus it can never be Nothing.

    Therefore, this syntax should be discouraged, as it relinquishes control over when objects are created. When you declare a variable with this syntax, an object will automatically be created any time that it is referenced in code and it does not already exist. This can cover up subtle bugs where you accidentally refer to an object variable that should not be instantiated at that point in your code. VBA happily creates a new object for you, and the code could (will?) do something unexpected down the line. These errors tend to be difficult to track down.

    In summary, in 'early binding' the compiler can figure out much of how to call methods and properties. With 'late binding' the generic interfaces talk to one another, figure things out, and get it to work. "Late binding" is very slow, but more flexible.


    With acknowledgements to Rob Bovey and Chip Pearson.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  13. #13
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by XL-Dennis
    Use adWChar as (if I remember it right the other types are not supported in MDBs) and specify the max length:

    [vba]
    .Append "FirstName", adWChar, 10
    [/vba]
    Dennis,

    Is that a 97 thing? adVarWChar works fine for me with 2000 (which is what Gibbo has?)

    Stanl,

    Useful, I will add the rest to my constants declaration
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  14. #14
    VBAX Mentor XL-Dennis's Avatar
    Joined
    May 2004
    Location
    ?stersund, Sweden
    Posts
    499
    Location
    Thanks Stan

    Bob - May I suggest that the tutorial will be a KB-article here at VBAX?

    Kind regards,
    Dennis
    Kind regards,
    Dennis

    ExcelKB | .NET & Excel | 2nd edition PED


  15. #15
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by XL-Dennis
    Bob - May I suggest that the tutorial will be a KB-article here at VBAX?
    Dennis,

    It is still in its infancy at the moment, it is part of a series on automation I am writing. I extracted this part as it seemed relevant to Gibbo. When a bit more complete, I will post it.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  16. #16
    VBAX Mentor XL-Dennis's Avatar
    Joined
    May 2004
    Location
    ?stersund, Sweden
    Posts
    499
    Location
    Stan - I have never figured why I've only been able to use adWChar although I use 2000 as well as 2003. It's simple bombs out...

    Bob - Great, as I believe more and more people will be interested in automation and therefore need some more knowledge and understanding. Pls let us know when You make it available at Your site.

    Kind regards,
    Dennis
    Kind regards,
    Dennis

    ExcelKB | .NET & Excel | 2nd edition PED


  17. #17
    VBAX Master Norie's Avatar
    Joined
    Jan 2005
    Location
    Stirling, Scotland
    Posts
    1,831
    Location
    I know this thread is solved but I notice nobody mentioned the Access Links Add-In. Is that because it's no use or because it doesn't quite do what the OP wants?

  18. #18
    VBAX Mentor XL-Dennis's Avatar
    Joined
    May 2004
    Location
    ?stersund, Sweden
    Posts
    499
    Location
    Hi Norie,

    I prefer to control things and add-ins like the Access Links add-in leave much out of my control. In fact, I have never used it

    Kind regards,
    Dennis
    Kind regards,
    Dennis

    ExcelKB | .NET & Excel | 2nd edition PED


  19. #19
    VBAX Expert
    Joined
    Jan 2005
    Posts
    574
    Location
    Thanks all for this thread, proving very useful

    Gibbo

Posting Permissions

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