Database name added to link tables!

Hi,
I am a very new Access database user.
I am using the Microsoft Access function called “Get External Data” to 
create link tables to an Oracle 11 G R2 database. My problem is that Access 
is adding the database name to the names of all the link tables instead of 
just creating link tables with the original Oracle database name. Eg a table 
on the Oracle database is called “com_user” but is created by Access as a 
link table called “databasename_com_user” 
Unfortunately all my automated test scripts that use the Access link tables 
fail because they do not recognize the link table names.

Do you know how I can remove the database name from the link tables or even 
better stop access adding the database name to the link tables when get the 
external data.

Many Thanks

0
Utf
5/13/2010 2:46:01 PM
access.gettingstarted 618 articles. 0 followers. Follow

1 Replies
538 Views

Similar Articles

[PageSpeed] 23

Dim db As DAO.Database
Dim tdf As DAO.TableDef

  Set db = CurrentDb
  For Each tdf In db.TableDefs
    If Left(tdf.Name, 13) = "databasename_" Then
      tdfName = Mid(tdfName, 14)
    End If
  Next tdf

  Set tdf = Nothing
  Set db = Nothing

(Don't forget to change the values 13 and 14 to correspond to the actual 
string you're trying for which you're looking)

-- 
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

"Lee7763" <Lee7763@discussions.microsoft.com> wrote in message 
news:A1C8D2B7-6CAC-4702-895F-F3CE3F2E4B22@microsoft.com...
> Hi,
> I am a very new Access database user.
> I am using the Microsoft Access function called "Get External Data" to
> create link tables to an Oracle 11 G R2 database. My problem is that 
> Access
> is adding the database name to the names of all the link tables instead of
> just creating link tables with the original Oracle database name. Eg a 
> table
> on the Oracle database is called "com_user" but is created by Access as a
> link table called "databasename_com_user"
> Unfortunately all my automated test scripts that use the Access link 
> tables
> fail because they do not recognize the link table names.
>
> Do you know how I can remove the database name from the link tables or 
> even
> better stop access adding the database name to the link tables when get 
> the
> external data.
>
> Many Thanks
> 


0
Douglas
5/13/2010 5:29:33 PM
Reply:

Similar Artilces:

Access Database Conversion to Excel Database
I need to convert a downloaded database in Access format to an Excel format I can use on my desktop. Hi Greg One way Use Data>Import External Data in the menubar -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "greg" <anonymous@discussions.microsoft.com> wrote in message news:b0f301c3ec2b$2b12d830$a001280a@phx.gbl... > I need to convert a downloaded database in Access format > to an Excel format I can use on my desktop. ...

Nothing shows up in Database objects in Database Wizard
Hello, I am having touble with Visio 2003 once again. Though the db wizard, I created an excel spreadsheet (table) for data and am tryinig to link the chart back to this original database so that when I change something in the database it will change correspondingly. I am going back through the DB wizard, and can only get as far as selecting a database object to connect to. I have selected the original dada source, checked table, but nothing showes up under the database objects. Am I or my computer missing something? Please help! Thank You. Have you defined a name for the region ...

remove from Pivot table items deleted in the source data sheet
Hi, I deleted large amount of records from the data source, but the corresponding items still show up in the drop-down list box of the row field in the pivot table. Does anybody knows how to remove these items ? I already tryed re-defining with the wizard the range of cells that contains the records. Thanks! Hi see: http://www.contextures.com/xlPivot04.html >-----Original Message----- >Hi, >I deleted large amount of records from the data source, but the >corresponding items still show up in the drop-down list box of the row field >in the pivot table. Does anybody kno...

Exchange 2003 + Outlook, adding disclamier message
Hi all, As following KB317680 "How to add a disclaimer to outgoing SMTP messages in Visual Basic script". I noted that from the end of the KB: Note If you use a MAPI client such as Microsoft Outlook to send the e-mail, the recipient does not receive a modified message. This is because messages submitted using MAPI are not in SMTP format when the e-mail triggers the SMTP transport event. Therefore, changes that are made by the event's code are not persisted. What should i do if my site is using Microsoft Outlook to send and receive email, how to add the disclamier message? Pl...

customer table
which table in sql contains the customer name, customer number, address phone etc.. The RM00101 table is the RM Customer Master the RM00102 table is the Customer Address Master. To locate table names go to Tools>>Resource Descriptions>>Tables select the elipses button next to the Table field, Select the Product you want (Microsoft Dynamics GP in this case). Select the series and the tables are listed in the scrolling window. Double click on a table and a window will open showing you all of the SQL field names in that table. "Michael@nyresume.com" wrote: > wh...

#Name? error in Formula
Hi there As I mentioned in the subject line, I am currently getting a @Name? error in a formula. The formula I have is =DateDiff("yyyy",[DateofBirth],Date())-IIf(Format(Date(),"mmdd")>Format([DateofBirth],"mmdd"),0,1) I noticed that the field should be [Date of Birth] but when I try to edit the formula, the formula change does not stick. How do I get the formula to stick? Thank you for your help in advance. What is the name of the text box in question? Is it DateofBirth? If so, try changing it to txtDateOfBirth. Then see if Access wi...

Is it possible to automaticly name a new sheet 22/10/2003
Once every day a new worksheet will be added to my workbook and I wonder if it is possible to automate the process of naming the sheets. I would like the new sheets to be given the name of the day they were created e.g 22/10/2003. Is it at all possible. Thanks in advance. -- Regards, Marvin Hlavac Toronto, Canada Try naming the sheet with 22/10/2003. It will list the illegal characters that you can't use. That said, this will add a sheet and name it. Sub AddSheet() Dim wSht as WorkSheet Set wSht = WorkSheets.Add wSht.Name = Format(Date, "ddmmyyyy") End Sub ...

How do I link to a workbook whose name is variable?
I want to create a link from one workbook to another based upon variable data. For example, if the cell A1 contains AAAAA then the link should be to a workbook named AAAAA.xls if the cell A1 contains BBBBB then the link should be to a workbook named BBBBB.xls Is this possible in Excel 2000? Something like this =INDIRECT("'" &A1&"Gantt Chart'!$A$7") with a name style of [Gantt.xls] in the cell, but it will only work with open workbook. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Carl Borthwick" <Carl ...

How to make check box for select specific field form table?
Greeting, I would like to create a form that able the user to select fields using check box and allows it to print the specified fields. For example, I have Student table and it has the following fields: First name Mid name Last name Age Group No. ….. Suppose that a user wants to display or print only first name, last name and group no. How can I do that please??? Should I use a query or form or what?? Many thanks You will need to be comfortable writing VBA code to achieve this. Design the report so it has all the fields you could wish to display. Design the form where the user selec...

Huge database
Good afternoon, i have to make a huge database wich have to get data from excel file.That is not a big problem problem is that i have to get information for about 1000 families with 1000 elements for every family and this database have to storage this information at leaset one year. This make a huge pack of elements. Can Microsoft Access get so huge pack of data ? If yes i would be glad to read some ideas. Thanks If you are talking about 1000 columns in a table, Access can't do it. 255 is the upper limit. 1,000 * 1,000 is potentially a million records. However if the data is pr...

linking 2 databases in 1
Hi, I am running 2 hqs for 2 different purposes, now the company decided to use 1 hq for both purpose. How can i link the 2 databases into 1 database???? I tired to link them from SQL server enterprise manager-all task- export data from database A to B but some tables can not be exported and show me tables failure, is it possible to link them or im just trying something impossible? Advice me on that plZzzzZZzz -- Aliko ...

Importing Current Database into CRM Database
Hi, I would just like to know if it is possible to tranfer my current database into the Adventure Works Database(and if so how) or is it used just for a few records for testing purposes. You help will be highly appreciated... there is a redeployment tool on support.microsoft.com/downloads under microsoft crm. This may help you. -- John O'Donnell Microsoft CRM MVP http://www.mscrmfaq.us "Rock619" <rodger@intervigil.com> wrote in message news:5728edd40161d2cca1c0e079cfd86665@localhost.talkaboutsoftware.com... > Hi, > > I would just like to know if it is pos...

how to start a database
hi guys, i want to start a database on excel 2000, i never had to do this before, and i know this can be done. i just dont know where to start from ! what i want to do is, it is a racing club, i want a listing of all pilot and a sheet where we can enter the number of the car(all pilot info need will be transfer) for final result of the race.(right now they enter the car no, name , city at every race at every registration, if he is register to 3 race they do the process 3 time.) like in access went we create the database and after the query, that what i want to do in excel. any tip or link...

Disable Auto Name Checking
Is there a away to Administratively disable the Auto Name Checking on Outlook clients. I need to do this for several PCs, and I would like to do it so that they can not manually re-enable the feature. Does anyone know of a way to do this? I would also like to disable the ability to turn Auto- archiving on as well. Is there a way? ...

Use query from another database
Hi In the code below is there a way that qryOrderHistory could be in a separate database. If so how would i refer to it in the code. Thanks Dim db As DAO.Database Dim qd As DAO.Querydef Dim rs As DAO.Recordset Set db = CurrentDb Set qd = db.QueryDefs("qryOrderHistory") qd.Parameters("prmCustNo") = lngCustNo Set rs = qd.OpenRecordset() If Not rs.Eof Then .... code to populate an excel spreadsheet Change your 'Set db = CurrentDb' line to something like the following .. Set db = DbEngine.OpenDatabase("full path and name of target MDB here") -- ...

Database Queries with database views, that base on many database t
I created 2 database views. Each of them uses 256 database tables. From every database table only one column is used in the select list of the view. Every database table has one column which is used for the join. Each database table has 5 rows. The database tables have not any foreign keys and indexes. Both views works fine. When I use the database views in a query like this: SELECT v1.*, v2.* FROM v1, v2 WHERE v1.id=v2.id I should get 5 rows with 512 columns in the resultset. But I get the following error from the SQL-Server (after about 5 minutes): Msg 8621, Level 17, State 2...

EXCH 5.5
I have a test server which has exch 5.5 installed. I use this for test purpose only. I want to bring pub.edb and priv.edb from my production exchange and try to repair the folders. My test exchange and production exchange, both have different site and organisation. How can I change or add new site/organisation on my test exchange so I can use production exchange's public folder ? Uninstall and then Reinstall with the correct org and site names. -- Hope that helps. ------------------------- Jaclynn Hiranaka Enterprise Messaging Support This posting is provided "AS IS"...

1 Keeps appending to Contact Name's Business Fax Number
I'm using Office XP and whenever I build a contact it appends a 1 to the front of it. I have gone through "Dialing Properties" but have been unable to get rid of it. Does anybody have any ideas? -- Paul Bergson Open your Contacts folder Press Ctrl Shift D Select Dialing Options Is the option to add the coutry code selected? -- PATRICK REED [Outlook - MVP]~~~~~~ -Microsoft Certified Professional (MCP) -Have you checked http://www.slipstick.com? -Please post your Outlook version! "Paul Bergson" <pbergson@mnpower.com> wrote in message news:%23BO...

how do I make a number entered in one table subtract from another
I made a table to track quantity of personal protective equipment, and another table to track who the equipment was issued to and the quantity given. How do I make the quantity issued subtract from the quantity on hand? You don't have enough tables! You need: TblPerson PersonID FirstName LastName <other person fields as needed> TblPersonalProtectiveEquip PersonalProtectiveEquipID PersonalProtectiveEquipItem PersonalProtectiveEquipInventory TblPersonalProtectiveEquipDistribution PersonalProtectiveEquipDistributionID PersonID PersonalProtectiveEquipID Personal...

Impact of changing database column width?
Hi, We are a software product company and are planning to begin using the Contract Administration module. To do so, we will be switching our inventory items from Track: None to Track: Serial Numbers and we will be storing our software license keys in the Serial Number field which is column SERLTNUM in table SOP10201. Unfortunately, SERLTNUM is CHAR(21) and our license keys are a few characters wider than 21. We can get the keys down to 21 characters by removing three embedded hyphens, but there are several databases outside of GP where the license keys must still exist with...

Program stops (creating pivot table) when too many records
Hi all, I have a macro which creates a Pivot Table using the records I provide and it works fine up to probably 40,000 records. When the database becomes too big, the program stops at the line of code which creates the Pivot Table with an error message. 1) Pressing F5 simply gives the same error message. 2) Running the same macro from start with a reduced records works well again. How can I overcome this problem? Thanks in advance. On Mar 5, 7:57=A0am, ch <c...@discussions.microsoft.com> wrote: > Hi all, > > I have a macro which creates a Pivot Table u...

Set Database in Excel
In Microsoft article 183446 under method 2, it refers to using "Set Database" on the Data menu. I am using Excel 2002 (the article says it applies to Excel 2002), but Set Database does not appear in my Data menu. Is this an error? It's an error due, I think, to very old menu structures. The article says that the information applies to everything from version 5.0 (1995) through 2003, inclusive. But I believe that the Set Database menu item disappeared from the Data menu as of Excel 97. And one has long defined a name using the Insert menu, not the Formula menu. But my rapidl...

Adding separate accumulators for multiple cells
I've learned to add an accumulator to multiple cells using the code on http://www.mcgimpsey.com/excel/accumulator.html Private Sub Worksheet_Change(ByVal Target As Excel.Range) Static dAccumulator As Double With Target If Not Intersect(.Cells, Range("C8:O9")) Is Nothing Then If Not IsEmpty(.Value) And IsNumeric(.Value) Then dAccumulator = dAccumulator + .Value Else dAccumulator = 0 End If Application.EnableEvents = False .Value = dAccumulator Application.Ena...

upgraded from MSSQL 2000 to MSSQL 2005 now database's users have
Hello. I updated the MSSQL 2000 server to MSSQL 2005 server now the database's users have no login names. I found a way to list orphaned users sp_change_users_login 'Report' but it only listed dbo and there are actually five listed with MSSQL Server Management Studio Express. I tried this sp_change_users_login 'update_one', 'RPS', 'RPS' but I get the following error the user name 'RPS' is absent or invalid. Any ideas? Try with Auto_Fix instead; see the example at the end of the following article: http://msdn.microsoft.com/en-us/...

Client access problem after databases restoration
Hello! Recently we had a problem with an Exchange 2003 Back-End server with 6 mailbox store distributed thru 4 SG, and we lost the databases because the SAN's disks partitions were deleted by mistake. So, we had to do a restore from a backup, but when we could not mount the stores, so we used eseutil /p command, and after, eseutil /d, and then isinteg -s servername -fix -test alltests. We could mount the stores, users can use email. The problem now is that we have some users that could not access their mailbox thru Outlook, and when we try to move to another store, we receive an ...