Create external database

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.
0
Utf
12/22/2009 8:14:01 AM
access.modulesdaovba 1670 articles. 0 followers. Follow

7 Replies
3832 Views

Similar Articles

[PageSpeed] 13

hi Ronald,

On 22.12.2009 09:14, Ronald wrote:
> I need to create an external database (file). On a site I found this code:
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/details.aspx?familyid=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en


mfG
--> stefan <--
0
Stefan
12/22/2009 9:57:32 AM
Sorry to argue, Stefan, but shouldn't it be Microsoft.ACE.OLEDB.12.0 for the 
Access 2007 accdb format?

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Stefan Hoffmann" <ste5an@ste5an.de> wrote in message 
news:OSX5v0ugKHA.5528@TK2MSFTNGP05.phx.gbl...
> hi Ronald,
>
> On 22.12.2009 09:14, Ronald wrote:
>> I need to create an external database (file). On a site I found this 
>> code:
> 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/details.aspx?familyid=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en
>
>
> mfG
> --> stefan <-- 


0
Douglas
12/22/2009 1:16:19 PM
hi Doug,

On 22.12.2009 14:16, Douglas J. Steele wrote:
> Sorry to argue, Stefan, but shouldn't it be Microsoft.ACE.OLEDB.12.0 for the
> Access 2007 accdb format?
No problem, hmm, I think this is what I have written, but maybe I don't 
get your point here.



mfG
--> stefan <--
0
Stefan
12/22/2009 1:42:22 PM
Thank you very much Doug and Stefan.
0
Utf
12/22/2009 5:43:01 PM
"Stefan Hoffmann" <ste5an@ste5an.de> wrote in message 
news:eqGDYywgKHA.1236@TK2MSFTNGP04.phx.gbl...
> hi Doug,
>
> On 22.12.2009 14:16, Douglas J. Steele wrote:
>> Sorry to argue, Stefan, but shouldn't it be Microsoft.ACE.OLEDB.12.0 for 
>> the
>> Access 2007 accdb format?

> No problem, hmm, I think this is what I have written, but maybe I don't 
> get your point here.

You wrote Microsoft.Jet.OLEDB.12.0

It's ACE, not Jet.

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)



0
Douglas
12/22/2009 9:45:47 PM
hi Doug,

On 22.12.2009 22:45, Douglas J. Steele wrote:
> You wrote Microsoft.Jet.OLEDB.12.0
>
> It's ACE, not Jet.
Ah, I see it now, my mistake. Sorry for that.


mfG
--> stefan <--
0
Stefan
12/23/2009 8:45:57 AM
Well, if you want to learn SQL Server and ASP.NET, you can make this work, 
btu you've got your work cut out for you.  Check out these videos:
http://www.asp.net/learn/videos/video-6554.aspx
http://www.asp.net/learn/videos/video-14.aspx
http://www.asp.net/learn/videos/video-49.aspx

All of these:
http://www.asp.net/learn/data-access/#editingdatalist

You can get VWD Express 2008, free, and SQL Server Express 2008, free.

-- 
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Ronald" wrote:

> Thank you very much Doug and Stefan.
0
Utf
12/24/2009 4:59:01 PM
Reply:

Similar Artilces:

Create Backup
I intend upgrade to sp3 from sp2. I have Win xp pro 7. Microsoft advise automated backup. I have read support info on creating backup. I inserted floppy and followed instructions for system backup. Estimated time was around 6days to complete ? It filled floppy quickly and was asked to insert another. At this rate I would need dozens. Any advice please on above. Regards Roger ----------------------------------------------- -------------------------------------------- "Roger" <Monkey@Zoo.fsnet.co.uk> wrote in message news:OifiSQYDLHA.5476@TK2MSF...

Creating a large number of charts automatically (repost)
Hi there, Are there Excel charting gurus here?? If so then please read on... Sorry for the cross-post but I'm not familiar with the Excel groups. I've posted to asp.general because if I have to code a solution to this it'll probably be done in ASP on a web server, unless there's a significantly better way. I'm looking for a way to create over 100 Excel *charts* programmatically. We've just run a large online survey and the data is being fed back to us in a single spreadsheet - each respondent on a row and their dimensions (division, department, etc) and each of their...

HOW TO CREATE MULTI-PROCESS&#12288;in a MAIN PROCESS
Q: As title ("lmzas" <anonymous@discussions.microsoft.com> message news:040d01c3d10a$c639f0f0$a401280a@phx.gbl...) A: The Windows SDK, the Visual C++ runtime library and MFC have some measure of threading and/or process functionality. I suspect that you mean thread when you say process but let's start with the process stuff. From the SDK, look up the help for CreateProcess (either at the MSDN library web-site, in your own copy of the MSDN library, http://msdn.microsoft.com/library/default.asp, or in your own copy of the SDK documentation) and scroll to the bottom of th...

Database Query #2
How do I return trimmed (i.e without spaces) result of a field with Database Query in Excel? Thanks. ...

outlook 2003 calender permissions downgraded but creates problems
User was permitted author permission to another user's calender and then down graded to reviewer. Now the user is receiving warnings they can not create item in the other calender or receives email meeting is not in other user's calender. The schedule meeting is in the other user's calender. Summation: User is able to schedule meetings but receives pop ups or email warnings. User does not have permission to the mailbox but the user can view the other user calender with no problems. ...

Auto-create activity from Mobile
Hi all, We have CRM 4.0 Roll Up 7. We are looking for a solution that offers advanced mobile integration to CRM. We would like the mobile client to auto-generate an Activity in CRM whenever a phone call is made to a CRM contact. Has anyone done this? //Johan Johan; There are a few vendors that offer good mobile solutions. I know for certain that MobileAccess from Ten Digits (http://www.tendigits.com/) has that specific feature. Dave Ireland "Jobro" <Jobro@discussions.microsoft.com> wrote in message news:4F1B3A1D-E23E-4E49-933F-B221C0DD1ECE@microsoft.com... >...

broken references with shared database
We are sharing a .mdb file on the document server. Some users are in Access 2002, others in Access 2007. Problem: reference is made to Microsoft Excel Object Library 10.0 on the 2002-PC. If the 2007-PC opens the database, Access changes the link automaticly to Microsot Excel Object Library 12.0 But if after that a 2002-PC opens the database it results in a Broken Link Error. How can I solve this? Tks Michel Peeters wrote: > We are sharing a .mdb file on the document server. > Some users are in Access 2002, others in Access 2007. > Problem: reference is made to Microsoft Excel Objec...

Create Hotkey to Open Form?
How do I create a hotkey to open a form? In one control on a form, I'd like the "+" key on the numerical keypad to open a certain form, or cause the OnClick event to run. MS Access 2002. -- croy Use the KeyPress event of the control: Private Sub SomeControl_KeyPress(KeyAscii As Integer) If KeyAscii = 43 Then DoCmd.OpenForm "Some Form" KeyAscii = 0 ' Stop the + from going to the control End If End Sub Plus whatever other code you need. HTH John ################################## Don't Print - Save trees croy wrote: > How do I create a ...

Create just an email user
How do I create an AD user that may be used only to access its mailbox (and not to login to a client computer)? What about removing it from "Domain Users"? Thanks! You can't. -- Ed Crowley MVP "There are seldom good technological solutions to behavioral problems." .. "Gaspar" <noreply@nospamplease.com> wrote in message news:uxLkgA6kKHA.2132@TK2MSFTNGP05.phx.gbl... > How do I create an AD user that may be used only to access its mailbox > (and not to login to a client computer)? > What about removing it from "Domain ...

Creating Date Range in Pivot Microsoft Query
I have a pivot that I've connected to our SQL db using the MS Query. I'm having two problems with it: 1. In Access I create a date range to see dates between 3 and 15months ago with the SQL statement " WHERE (Trx.TrxDate) Between (Date()-485) AND (Date()-120)" - no problem in Access but not the MS Query program. When I do it through the Excel MS Query it says the Function 'date' is not valid. How do I describe the current date in the MS Query program? 2. In the same wizard I cannot combine two fields (in Access I would use Expr1: [Field1]&", "...

Creating a pie chart
I am trying to create a pie chart using four categories that 8 people ranked the categories using 1-4 by which is more of a priority. First you'll need to get the data into a form of Cat 1 Data for Pie Cat 2 Pie Data Cat 3 Pie Data Cat 4 Pie data "Val" <Val@discussions.microsoft.com> wrote in message news:2C382264-3DCF-41EF-BDA8-742F797CC3D1@microsoft.com... > I am trying to create a pie chart using four categories that 8 people ranked > the categories using 1-4 by which is more of a priority. ...

Exch 2000 Block External Mail from / to selected recipient(s)
Hi - Question(s) from an Exchange 2000 Newbie 1. I want to prevent mail messages from & to a list of specific email addresses. Do I use Recipient Policies against a specific account? 2. I have some new staff joining & may wish to prevent external mail exchange - permitting email between internal Outlook users. Where do I find settings to prevent external email transmission or receipt? -- Kind Regards from Nick Ambay Software I can't understand what exactly you to do. But if what u want is to restrict the particular user on sending and receiving email on the particular sender y...

Creating Run Time Controls
I am creating a checkbox at run time, as shown below. Spy tells me that the checkbox is receiving messages, but it doen't respond to snaps by checking and un-checking, nor does it receive BM_SETCHEck messages like the check boxes I create from a Dialog resource. How can I get these run time check boxes to respond? CButton *m_mycheckbox; // Member of CMyDialog BOOL CMyDialog::OnInitDialog() { *m_mycheckbox; = new CButton; *m_mycheckbox; ->Create( _T("My Checkbox Name"), WS_CHILD|WS_VISIBLE|SS_LEFT|BS_CHECKBOX|BS_LEFTTEXT...

Problem creating a document
Hello, Thanks for the advice but what I am trying to achieve didin't work! I work in an estate agents office and we publish our own property list using microsoft publisher, which is about 6 x A4 pages of detail which has a thumbnail picture of a property with a breif description of text. There can be 50 properties on this list and they are all in price order. When a new property comes on for sale ie: £100,000 I may have to insert it between a property priced at £95,000 & one priced at £105,000. To insert this what I currently have to do is move every single propert...

How to create a certain rule??
How can I create a rule that every time a users sends an email it also sends a copy of that email to another users inbox? I tried using the RULES wizzard but that didnt seem to help. Im also not sure if this is done on the Exchange server. User has OFFICE XP - 2002 You already posted this in the outlook.general group were I've answered it. Do not multi-post but simply address more than one (if relevant) newsgroup in a single post -- Roady [MVP] www.sparnaaij.net Microsoft Office and Microsoft Office related News Also Outlook FAQ, How To's, Downloads and more... Tip of the mo...

Creating "Subaccounts"
Hello, I am looking for software that will let me create what I would consider subaccounts. The way I currently track my money in my personal checking and savings accounts is with an excel workbook. The way I have the workbook set up is I have a page for my checking, and one for my savings. On the checking page, for an example, I then have multiple columns for each bill that I pay from my checking, and for my spending money. I then have it suming the columns up at the top to show me how much I have set aside for each bill. These sums are the added to tell me how much is in m...

Create Table after a record is inserted
Hi, I have a table with the name of all my suppliers. Every time I add a new supplier I want a table with a standard structure and name of the spesific supplier to be created. Is it possible to be done? Thank you, Leon So you want a table for every supplier or you want the suppliertable constantly duplicated. I don't think that's following the normalization rules and you might be in a lot of trouble with maintenance on your db. -- Maurice Ausum "Leonidas" wrote: > Hi, > > I have a table with the name of all my suppliers. Every time I add a new > supp...

Importing Data and Creating Charts from Spreadsheet
Greetings - Sorry if the subject is not very explanitory, wasn't sure how to word it. Basically my issue is I am trying to create some different charts from a spreadsheet where there might be multiple rows for the same values that will go on the X axis. For example this spreadsheet might have the following Data: ABC Company $500 BCD Company $332 XYZ Company $500 XYZ Company $200 When that chart is created (company on x axis, amount on Y axis) it creates 4 different columns (one each for ABC Company and BCD Company and two for XYZ Company). What I would like it to do is to crea...

Money creating duplicate Credit Card account
Everytime I perform an on-line update to my accounts, Money creates an additional copy of one of my CC accounts. This particular account does not appear to be and is not supposed to be one of those that updates. I am not able to merge the two accounts. When I try to do so, the choice of that account is not available. I found an online solution, which was essentially to merge the two, but that does not work. Does anyone have any ideas for me to try? Probably more information is needed to answere the question, such as how the accounts are being named, etc. Check the account settings...

odd files created
Every time I open and edit an excel spreadsheet on a network share, small odd files get created. They are usually no larger than 25-40k and don't have any extensions to them. Looking at the properties page for any file, the file description says File. Anyone know what this is from or how to get rid of them? Permissions are setup correctly for me, Word files don't have this behavior. Excel 2000 SP3 Thanks, ...

Constant "Entourage cannot access your data. To attempt to fix the problem rebuild your database." error
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Email Client: Exchange I have been getting the above error almost constantly for the last month or so. <br><br>Every time I verify the database, it says it's fine and I have rebuilt the database several times too. <br><br>I have also created a new identity a couple of times and that seems to fix the problem for a day or so then I start getting the error again. <br><br>I do see able to access my email and identity if I just ignore the error messages, but am concerned that this ...

how do I find a list of databases with correpsponding company name
Hello, I need to know how in GP to pull up a list of our companies with their corresponding SQL database names. I've done this a long time ago and remember it being easy but cannot remember where to do this. SELECT INTERID, CMPNYNAM FROM DYNAMICS..SY01500 "Tim Quiggle" <Tim Quiggle@discussions.microsoft.com> wrote in message news:D0ECF5C5-74F9-494E-8C2E-B37EE12C8D0C@microsoft.com... > Hello, > > I need to know how in GP to pull up a list of our companies with their > corresponding SQL database names. I've done this a long time ago and > rem...

How to create an expandable menu on the left of the sheet?
Hi I saw a template with an expandable menu on the left of the sheet. This way I don't need to create multiple sheets - just extend or collapse a content, like in MS Windows Explorer folders. Thanks See the help file for "Outline" "pacific" wrote: > Hi > > I saw a template with an expandable menu on the left of the sheet. This way > I don't need to create multiple sheets - just extend or collapse a content, > like in MS Windows Explorer folders. > > Thanks Thanks a lot. "Ray A" wrote: > See the help file for "Out...

Unleash XML support with Oracle database
Download Oracle Data Provider for .NET (http://otn.oracle.com/tech/windows/odpnet/index.html) today to empower your .NET applications with the Oracle Database! Try out the new features for XML support like: - Store XML data natively in the database server as the Oracle database native type, XMLType. - Access relational and object -relational data as XML data from an Oracle database instance into Microsoft .NET environment, process the XML using Microsoft .NET framework - Save changes to the database server using XML data. Developers get started, learn a...

Creating an outlook plug-in
Could anyone point me in the direction of some information on creating outlook plug ins? I want to create a tool, using VS .NET, that would read the mail as it comes into Outlook and place it in a folder based on certain criteria. I don't know where to begin. Thanks, Chris You might want to look around at http://www.outlookcode.com/d/index.htm for lots of ideas. -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the newsgroup to preserve the message thread. *** In news:%232RLGRMIEHA.3240@TK2MSFTNGP12.phx.gbl, Ch...