Solved: Create access database from excel?

[gibbo1715]

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

[Bob Phillips]

You can easily do it using ADO/ADOX. There is an MSDN article

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odeopg/html/deovrcreatingmodifyingaccesstables.asp
Creating and Modifying Access Tables

[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

[Bob Phillips]

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


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

[gibbo1715]

thanks xld

but i get an error authentication failed

I have access 2000 installed on my computer

Cheers

Gibbo

[Bob Phillips]

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?

[gibbo1715]

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

Sub Button1_Click()
#If EarlyBound Then
Dim oADOCat As ADOX.Catalog (http://trafficsector.com/new/ezula_proc.php?uid=923541&ezid=122501&elid=10042#do_redir)

DBPath = "C:\Test (http://trafficsector.com/new/ezula_proc.php?uid=923541&ezid=121409&elid=9156#do_redir).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


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

Thanks in advance for being so patient with me

Gibbo

[gibbo1715]

Real Sorry XLD, Having just looked at it on the forum see my obvious mistake

Sub Button1_Click()

DBPath = "C:\Test (http://trafficsector.com/new/ezula_proc.php?uid=923541&ezid=121409&elid=9156#do_redir).mdb" ' <<<<<<<<<Moved
#If EarlyBound Then
Dim oADOCat As ADOX.Catalog (http://trafficsector.com/new/ezula_proc.php?uid=923541&ezid=122501&elid=10042#do_redir)


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

This now works, sorry for waisting your time,

I do feel like an idiot

Gibbo

[gibbo1715]

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?


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

Thanks

Gibbo

[XL-Dennis]

Gibbo,

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


.Append "FirstName", adWChar, 10


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


Kind regards,
Dennis

[stanl]

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

[Bob Phillips]

A tutorial http://vbaexpress.com/forum/images/smilies/001.gif

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.

[Bob Phillips]

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


.Append "FirstName", adWChar, 10


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 http://vbaexpress.com/forum/images/smilies/001.gif

[XL-Dennis]

Thanks Stan :)

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

Kind regards,
Dennis

[Bob Phillips]

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.

[XL-Dennis]

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

[Norie]

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?

[XL-Dennis]

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 :whistle:

Kind regards,
Dennis

[gibbo1715]

Thanks all for this thread, proving very useful

Gibbo