Create external database

R

Ronald

Hi all.

I need to create an external database (file). On a site I found this code:
------------------------------------------------------
Function CreateLinkedExternalTable(strTargetDB As String, strProviderString
As String, strSourceTbl As String, strLinkTblName As String) As String

'strTargetDB = Source Database Name
'strProviderString = Not used, currently hard coded
'strSourceTbl = Source Table name in the database we are linking too.
'strLinkTblName = Table name we would like to see in the Access Database.
Dim catDB As ADOX.Catalog
Dim tblLink As ADOX.Table

Set catDB = New ADOX.Catalog
'Open a Catalog on the database in which to create the link.
[catDB].ActiveConnection = "Provider=Microsoft.Jet.OLEDB.12.0;Data
Source=" & "C:\Database\Test.accdb" 'strSourceDBFile
If ([catDB].ActiveConnection.State <> acObjStateOpen) Then GoTo ErrHandler

Set tblLink = New ADOX.Table
With [tblLink]
'Name the new Table and set its ParentCatalog property to the open
Catalog to allow access to the Properties collection.
.Name = strSourceTbl
Set .ParentCatalog = catDB
'Set the properties to create the link.
.Properties("Jet OLEDB:Create Link") = True
.Properties("Jet OLEDB:Link Provider String") =
"Provider=ODBC;DSN=TestODBC;DATABASE=TestDB;UID=admin;Password="
'strProviderString
.Properties("Jet OLEDB:Remote Table Name") = strLinkTblName
End With

'Append the table to the Tables collection.
On Error Resume Next 'If table doesn't exist continue.

[catDB].Tables.Delete strSourceTbl

On Error GoTo ErrHandler

[catDB].Tables.Append tblLink
Set catDB = Nothing

Exit Function

ErrHandler:
MsgBox Err.Number & " " & Err.Description
Set catDB = Nothing

End Function
------------------------------------------------------
But when I test it I get error 3706 (Provider cannot be found. It may not be
properly installed.) on line: [catDB].ActiveConnection =
"Provider=Microsoft.Jet.OLEDB.12.0;Data Source=" & "C:\Database\Test.accdb"
'strSourceDBFile

It looks like '12.0' is not available but when I use '4.0' I get
'Unrecognized database format' error.
Is it possible to create an Access 2007 format database?

Thanks for your help,

Ronald.
 
S

Stefan Hoffmann

hi Ronald,

I need to create an external database (file). On a site I found this code:
Click to expand...
Instead of posting a bad formatted code, posting the link would be better.

btw, it's basically the same as

http://msdn.microsoft.com/en-us/library/aa164898(office.10).aspx
http://support.microsoft.com/kb/275249
http://msdn.microsoft.com/en-us/library/ms678060(VS.85).aspx
http://msdn.microsoft.com/en-us/library/ms681754(VS.85).aspx

See for formats:
http://support.microsoft.com/kb/283874

Microsoft.Jet.OLEDB.4.0 is the Access 2000 mdb format.
Microsoft.Jet.OLEDB.12.0 is the Access 2007 accdb format.

If your error happens on a machine without Office 2007 installed then
you need to imho install the following:

http://www.microsoft.com/downloads/...36-8C28-4598-9B72-EF94E038C891&displaylang=en


mfG
--> stefan <--
 
D

Douglas J. Steele

Sorry to argue, Stefan, but shouldn't it be Microsoft.ACE.OLEDB.12.0 for the
Access 2007 accdb format?
 
S

Stefan Hoffmann

hi Doug,

Sorry to argue, Stefan, but shouldn't it be Microsoft.ACE.OLEDB.12.0 for the
Access 2007 accdb format?
Click to expand...
No problem, hmm, I think this is what I have written, but maybe I don't
get your point here.



mfG
--> stefan <--
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to programmatically link Outlook Address book to Access Databa 0
creating linked tables using ADOX and C# 4
VBA Code - Records from linked tables and appending to unlinked table 1
Error 3251 using ADO & VBA to drop a table! 13
Refresh Linked Tables - ADO 10
Unable to run macro 1
adding a clumn to every table access2003 1
Create user in code 2

Top