Create external database

  • Thread starter Thread starter Ronald
  • Start date Start date
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.
 
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 <--
 
Sorry to argue, Stefan, but shouldn't it be Microsoft.ACE.OLEDB.12.0 for the
Access 2007 accdb format?
 
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 <--
 
Back
Top