I am trying to get a list of tables in my DB. I have used this SQL, but it doesn't give me the linked tables? What modification to the SQL do I need?
SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Flags)=0) AND ((MSysObjects.Type)=1))
ORDER BY MSysObjects.Name;
looking for a way to export the list of table names, table types & if they are linked (e.g. tbl Sales Linked .dbf or tbl Staff linked to excel) from a database - this has to be done for about 300 databases.
in an individual db, I have a make table query off of the table MSysObjects to get the data. The Database field tells me where the source of the linked table resides & the ForeignName field gives me an idea of the format of the data source (e.g. dbf or excel). I could manually import that query into each db, run it to get the table names, then copy & paste..
Hi all,
I started a new thread, because it is a new subject even though is related to my Price List thread, I hope is OK.
If someone can please take a look at my attached Excel Price List (particulary the factors sheet) and give me an opinion of how my tables should be created.
I cleaned the Price List was too big to attach and it will be easier to be understood, in its entirety is kind of all over the place, exactly why I need to make simpler in Access for another user to update if I am not around.
I am below including what I think the tables should include, but not sure exactly how they should be, please be reminded that I am un unexperienced newbie.
----------------------------------------------------------------------
tblSuppliers
SuppliersID
SupplierName
SupplierCode
tblMarkup
MarkupID
MarkupName
MarkupAmount
tblTaxes
TaxID ------Do I need This?
TaxName
TaxAmount
tblExtras
ExtrasID
ExtrasName
ExtrasPrice
SupplierID
tblDiscounts
DiscountID
DiscountName
DiscountAmount
SupplierID
----------------------------------------------------------------------
Thanks for any help,
Emilio
Hi,
I wonder if there is a possibility to select a table from a database by using a sort of explorer window.
My problem is this:
I want to import a *.xls file in a table.
I want to be able to select a certain *.xls file from a list (so far no probs) en add it contents to a table selected in a browser window.
A table ofcourse who the exact field structure.
Anyone?
hi,
can anyone please tell wht's the query to check if database exists, if exists list all tables in database (in sqlserver) else raise error
Thanks in advance,
Deepthi
Hello,
I am putting together a database for a charity and am having some problems. At present I am working seven days a week and my time for research is so limited that I can only ask here for advice since I have neither the time nor resources to seek it out myself.
We do mail shots to various trusts and businesses to appeal for donations. At present we have two large, bloated MS Works Databases (one for Trusts and one for businesses) that contain names, addresses and so on. I have taken on the task to make our mail shoots more efficient. My vision is that the majority of time should be spent searching out new business/trusts and organising events while actually doing the mail shoots should basically be done at the touch of a button (it won't quite work like that, but previously the actual mailshots took up about 80% of the time. I want to drive that number down)
Having no Access experiance whatsoever I set about creating a Database with twelve tables, one for each month. Each month would contain a list of places that we would "hit"
I then came across a problem. When I am searching out new addresses I would have to cross reference them on 12 different tables. This struck me as being impractical but at the time of creation I didn't have the foresight to create a masterlist. Although I do have the list on my Works DB, it would be useless as time passed since no more entries would be inputted on there.
I was hoping someone could tell me how I can put together a masterlist using the data I already have and maybe if possible setup the 12 tables in such a way that by inputting an entry on one of them will automatically crossover the name of the business to the masterlist without me having to enter it twice.
I realise this may sound confusing, and if i had more time I would research the answer myself but I am stretched to the limit and know too little about this application. I did try to take on a Lyndia (sp?) course but again I just don't have the time. If you have any questions, I will check back later to answer them.
I am using Access 2002 (but my DB is in Access 2000 format - so it says)
Thanks kindly.
I have a db which enables users to enter expenses.
tblExpense
tblExpenseDet
tbleExpenseMileage
tblExpenseDet & tblExpenseMileage are joined to tblExpense
As the expenses are entered at User Level not Client Level, I need to extract the Client ID from the 2 sub tables to create a list grouped by client so we can report the billable expenses.
I'm thinking union query but wouldnt know where to start.
Any help would be appreciated
Ta
Hi all,
I need some help with regards to a small db application im developing.
I currently have a form that contains two separate list boxes.
The first contains "Measures" and the second "Dimensions". They are based on the underlying tables Measures and Dimensions respectively.
My intention is to be able to select whatever Measures I want and select the dimensions they relate to in the other list box then click on a command button to run a query that would update/append my underlying table, Measures2Dimensions, which is linked to the tables that the list boxes are based on.
I hope I've described my problem well enough for someone to provide me with some help.
Many thanks in advance!
Hi just started working for a Company and I found they have tons of
databases but no structure or standard, so I would like to build a tool that will go thrue a list of databases and get all the table names, the items and type. Is there functions in vba that can get me a list of the tables in the database and the get all the items in the database ?
Thanks on any input
I want to validate the value of the list in the lookup in the table and create a list of the values that no match the values of the list..
View 3 Replies View RelatedIn my database I have a table for all of the customers emails which list the 3 titles for each district they serve, for example District 1 has across the President, Vice President and Clerk. each email is a hyperlink that when clicked once automatically takes you to outlook, however, how can I choose all 3 at once without having to go back and forth to the database and outlook after the one click? so all 3 recipients are included in the email at once. In Excel you can copy and paste the 3 emails but in Access it doesn't allow you to do that. How I can execute this?
View 5 Replies View RelatedI have created some tables using CREATE TABLE but they do not appear in the list of tables although they are there. How to make them appear.
View 1 Replies View RelatedI am after creating a list box that will display some of the tables within my database - there are between 10 and 15 tables and the names all follow the same pattern (They all start "tblD10").
View 1 Replies View RelatedI have recently acquired an Assistant Management position in a small restaurant and I have been given the task of creating a database of all of our Recipes with a master list of ingredients. I can easily set this up in Xcel but it is very messy and time consuming to say the least.
I would love to be able to create a master list of ingredients (1-X) With their unit of measurement (Pound, Gallon, Etc) as well as their unit price (pound of bacon= 5.00)
Now the recipes would be set up with the (ingredient-amount of ingredient-cost of amount of ingredient)
I would like to start a new recipe by opening a table, typing in a number that corresponds with an ingredient on the master table and those fields be filled in other than the amount of ingredient. Also by changing the price of an ingredient would automatically update on the recipes.
my boss wants to know how much each recipe cost as well as keep them updated when the price of ingredients change.
hi
is it possible, for any reason, to append another sql query after one sql query?
for example, the first one is "select name from bla bla where name=something" and we just add another query at the end of the query to make something like
"select name from bla bla where name=something and
then show table from this database"
btw i'm using asp to call all these queries..
any help is greatly appreciated..thanks :)
Hi
I am trying to compare data in two different tables and list the data that does not match. Not sure if this is possible. Details below
Table 1
xyz - A1 - Do not use
ABC - A4 - Use
Table 2
123 - A1
The result of the query should show me
A4 (as this does not appear in table 2
I am not too familiar with Null values as there will be many null values that i want to ignore.
Any help will be appreciated.
I've got a table of associate directors "t_ADnames" and want to build separate tables for each AD name that pulls a pass through query from our data warehouse. I'm thinking it's got to be done with a macro somehow? So it would run pass_query where AD name = "John" and insert into t_john, then it would check the next name in t_ADnames and run the same query for say "Mark" and insert all his data into t_mark and so on until the list (of about 12 people) has been completed.
View 13 Replies View RelatedI am importing data from a sharepoint list on to Access 2007, as linked data where any changes I make on Access is made to the list and vice versa. However, I recently made a change to one of the column types and this change is not being made on Access. I have changed a column type from choice to single line of text but I still see the list of choices when I access the list on Access 2007.
I have refreshed the list by right clicking the linked list and pressing refresh list. The list is set to not cache the list and is not set to work offline so cannot see why the change is not being made. I don't want to mess with the list be removing it and re-adding it as I have multiple queries set up which are being fed in to an excel file I use to create reports from the data. I have no knowledge of VBA, so there is none of that being used on Access.
I have a Table named TBLBookings...on one of the fields I have a Lookup wizard thats linked to a Table named TBLVehicles which includes
Car Reg
Car Type
Location
When I run the TBLBooking and click the drop down list it shows up
Car Reg
Car Type
Location
but once the field is clicked all it shows in the records is the REG where in a form id like to see what vehicle it is location etc...is it possible to have extra fields in the form named Car Type and Location and once the registration is chosen it automatically fills in the correct details for them?
I created a form with subforms that are all linked to their own tables. Values that are entered in to the form (i.e. just numbers or names) all save into the tables as I would expect; HOWEVER, all of my value lists (0,1,2 or 1-5) do not. I can change the values in the forms and as long as I am toggling between them they appear in the form but they are not populating the tables.
Other details. They are defined as numbers in the property table, I did change them to a value list in the form properties and did assign values to the property list.
I looked through some other threads that seemed similar but the solutions didn't apply. Because the straight up data entry saves, I feel like it is something specific to me creating those value lists...
I have a sizable list of GIS polygon shapes(~9000 data points), collected over 15 years, that I am trying to shrink down to unique ID's. Each years data has a unique label, but for many of the polygons the shape does not change from year to year, so I want to assign a unique ID that encompasses all of the years that shape existed. Some examples:
1950- Parcel A - 2 acre star same
1951- Parcel K - 2 acre star same
1952- Parcel L - 2 acre star same
1953- Parcel F - 2 acre star same
1954- Parcel J - 3 acre box same
1955- Parcel Z - 3 acre box same
Ok, simple enough. I created a unique polygon ID in a separate table, and set the first 4 records to some value, let's say AAAA. The latter 2 records I called something else, BBBB let's say. These values are used in a combobox so my data entry stooges can easily assign each duplicated polygon the same ID, so a quick query will show that polygon XXXX is comprised of so many years worth of the same value, just with a different label. Now my question...
In the above example, after 1953 the 2 acre star polygon never exists again, so I want to 'retire' that option from the combobox so it cannot be chosen in error. As it stands now, if I were to enter the following date point 1956- Parcel N - 3 acre box
I can still choose AAAA or BBBB as the unique polygon ID to equate it to. Is there some way (maybe a boolean option) to disable just a specific combobox value so it cannot be entered in new records, but STILL shows up in old records, a.k.a. is not deleted?
I found 2 examples of code to refresh my attached table link to sharepoint lists.
I have a scheduled task open my Access 2010 db and an autoexec macro runs and closes the db.
My problem is my tables disconnect from sharepoint 2010 and the update fails.
I added the code I found to a module and added a line in my macro to run the code. Now I cannot get the code to work.
Am I at least on the right track? I just want to know before I spend many hours getting this code to work.
These are the two locations of code I found.
[URL]
I think part of my problem is that I have a regular table that I add a txt file to that is not linked to sharepoint so I need to exclude it from the code.
What is the correct way to save the values in a listbox column to the fields of a table? I can use rec("field1") = Me.list16.ItemData(Varitem)and that records the bound column to field 1 but how can I save the unbound columns to other fields in the table?
View 4 Replies View RelatedI have to design a price list table which is currently done in Excel.
The table looks like this :
[Headers]
Product Group | Layers | (this are quantity bands) 0-100 | 101-500 | 501-1000| etc
Test1 | 2 | £10(normal) £9(special)
The quantity band currently is fixed to 5 bands but would need to be flexible. There are also 2 prices for each of the quantity band (normal/special)
At the moment my table design looks like this:
ID
fkSupplierID
fkProductGroupID
txtLayers (value list)
intMinQty
intMaxQty
curNormalPrice
curSpecialPrice
This works quite well with the query to return price based on product group, layer and order qty. However I am not very sure if this is the best way to design this. I am just thinking about maintenance - for example when the supplier puts in a price change or when the quantity band changes. The current format (quantity band) is based on a major supplier but in the future we would like to adopt this for any supplier.
I have three linked tables [Regulator], [RuleBook] and [Rules] (each has a corresponding form for data entry).
In the "Rules" form, when you select name of the regulator, the rule books dropdown down is filtered to show only the rulebooks for that regulator. the code I use is:
Private Sub Regulator_Change()
Me.[Rule Book].RowSource = "SELECT [ID],[Rule Book],[Short Code],[Regulator],[RegName],[Short Form],[Active] FROM" & _
" [RuleBook] WHERE Regulator = " & _
Me.Regulator
Me.[ShortReg] = Me.Regulator.Column(3)
End Sub
Basically, if I select the FSA as the regulator, The Rule Books drop down is filtered to only show the FSA Rule Books. If I pick OFT, I get the OFT list etc. This all works fine.
In the [RuleBook] table, each entry has a yes/no tick box called "Active".
The reason for this being that the regulators change their rule books quite regularly, but from an audit perspective, I can't delete or overwrite the old ones once they are no longer valid.
What I want to do, is further filter the rule book list to those where Active=True so that when I select FSA, I can only choose from their current rule books and not the ones that are no longer relevant.
I have a list of 22 soccer players with their weights sorted from heaviest to lightest in numerical order. I want to add further players and have the list reflect their standing each time it changes i.e. Col 1 Player ID, Col2 FirstName, Col 2 SecondName, Col 3 Mass:90 Kgs Col 4 Position In group: 1
A new player registers and is 95Kg.....he becomes #1 and the rest go down etc. Can this be done?