Why is it that only SOME of the tables in my database show up in the Entities pane?
There are a total of six tables in my database, yet only three of them are available to me in the Entities pane. I have looked at permissions, etc, and I am using the SA account. It seems like I should see either ALL or NONE of the tables, but that is not the case.
What am I missing?
Apologies if this has already been asked and answered, though I haven't found it via search.
In my report model I have a lookup entity that consists of a Code field (PK) and a Description field this is linked to a parent entity (table) and works fine, e.g. the description is shown in the parent entity instead of the FK code. However this lookup entity would be useful linked to many other entities (tables) which use the same FK code linked to the lookup entity's Code field (PK) for use of the Description field. In my first attempt I linked the lookup to a second table entity but unlike the originally linked table entity, this is not shown as the description in report builder, only the role appears and I have to select the role then the description from the 2 fields displayed (Code, Description), additionally I get shown the related first table entity.
Firstly can a lookup entity be linked to multiple tables?
Secondly, if so am I forgetting something so only the description is shown like in the first linked table entity?
Thirdly, if I correct the problem is there a property I need to enable/disable to stop the first linked table entity appearing in the second table entity entity list in report builder or should I just leave it?
Any help/advice/suggestions would be appreciated, and apologies for long list of questions but all the books and help files I have used don't seem to use imperfect/real life data sources and examples.
Thanks
Andy
I am new to sqlserver.
1)I created a linked server to a oracle database, works fine. Now I am writing queries, using "Query Desgner", when I do a "Add table" linked server tables do not appear. Is this a limitation. I can get around the problem by creating a view.
2) When I use "Microsoft OLE DB Provider for Oracle" for linked server, some times connection seems to get lost. On one occassion, the next day it was fine, it re-established itself.
Any ideas thnx.
I have a report with a Matrix table. When it€™s fully populated I have 5 rows. These rows are different categories in a CRM system. In these categories there are a lot of Opportunities listed. The thing here is that category 1-4 exist in one table, really a view, and category 5 exist in another different view. So to gather all the information into one single Matrix table to later have subtotals and stuff I just made a little Union of the two select queries.
Now one the column in the table is of course Topic. And from here my client wants to be linked to the right CRM card. The problem I have encountered is that how can I have to different addresses on one single cell in the table?
In the Navigation pane in the cell that corresponds to the Topic, for now I have an address like this:
=string.Format("http://{0}/SFA/opps/edit.aspx?id={1}", Parameters!srv.Value, Fields!objectid.Value)
What I need to change when it€™s a category 5 Opportunity is €œopps€? to €œcat5€?and add €œ&etc=10008€? to the end of the address. Any ideas?
PS. {0} and {1} are parameters, one server parameter and one opportunity ID parameter.
Kind Regards
When I change some data in a row in my Results Pane and hit enter, I receive a message that "Data has changed since the Results pane was last retrieved." I click the yes button to "commit your changes to the database anyway," but then I get an error message telling me that my row was not updated. The message says "Error Message: The updated row has changed or been deleted since data was last retrieved. Correct the errors and retry or press ESC to cancel the change(s)." This time, the only option I have is to click the OK button. Can anyone give me some idea as to what I need to do to be able to update the row data in this way? Thanks.
View 11 Replies View RelatedHi,
I’ve three questions, would someone answer them please,
I have 3 entities:
The book entity(BookNo,BookNmae,AuthorName,Price...etc), the MembershipCard entity(CardNo,MembershipNo,ClientNo,PublishDate,EmpNo..etc), and the Client entity(ClientNo,ClientName,ClientAddr,ClientPhoneBookNo,RequiredBookNo,..etc)
1-What is the relationship degree between them, is it ternary or binary?and Why?
I'm so confused ,my answer is ternary, I made a (1:m) relationship between the clients entity and the books entity (a client may buy more than one book), (1:m) relationship between the employee entity and the books entity (an employee may sell more than one book)and a (1:m) relationship between the employee and the Client(an employee may serve more than one client(is it right or it's (M:N)).
So is my answer is right or wrong, please help me to understand, I’m so confused.
2- Is the cardinality degree between the Client entity and the book entity , (0,N) for the client entity and (1:N) for the book entity??
3-What is the best model to use in designing data in general,Chen's model or Crow's Foot and why?I think it's Crow's one ,for it's more arranged and easy to read.
with thanks
deema
I created a report model with a single "data source view." The datasource view has three tables "Business, XRef, Employee" and a named query "PeopleCount" based on these tables. The smdl [design] view shows "Business, XRef,Employee" as "entities" and does not show the named query from the "data source view" (I don't know if that is normal). I published this report model to the reporting server (its showed "success").
I then used Report Builder to open the report model as a "chart", and it shows there is only one entity for me to play with, its called "XRef." Why do the other tables ("Business" and "Employee") not show up in the Report Builder's list of entities?
My own guess is that "XRef" is the only "actionable" table for making a chart. Indeed, I notice that among its "fields" is one called "Business Title" (which apparently comes from the Business table). But I also notice it does not have an "Employee Name." Why have the former and not the latter? Again, my own guess is that the type of the Employee.Name field, a nvarchar(MAX), might be considered unusable for reporting, since it is a "max" size. Thoughts?
I am using SQL Server 2005 RTM, which is running on Win 2K3 Server SP1.
HI
When you right click open table to return all rows, click on the "Show/Hide Grid pane" icon . When you click on any row the first column called "Column" a dropdown shows all the filed names of that particular table.
This feature used to work fine untill recently wherein when I click on the column, I don't get to see all the filed names but now it just shows TableName.* i.e. all columns.
This is the case when I try to create a view in enterprise manager also. When you add a table to create a view, I cannot see all the filed names . I'm not sure what to do , to get the field names to appear? Is it some kind of setting in enterprise manager.
All the info would be appreciated.
Ta
N
Is there any way to have the results pane auto hide ? Im lazy and dont want to manually (ctl-R) hide each time i run something, then starting writing code again
View 1 Replies View RelatedI am using the ReportViewer control with SRS2005. I have an extremely large report (243 pages) - however. It takes an extremely long time to load the report as well as populate the document map pane.
When one clicks a link in the document map pane, it refreshes the entire page - and thus causes the report to take another 2-3min to repopulate the document map.
However, if I were to access the Report Server directly, clicking a link only causes the main content page to be refreshed.
This is a substantial difference between selectively refreshing as opposed to refreshing the ENTIRE page and hence taking 2-3min between links.
I am defining the report as:
<rsweb:ReportViewer ID="rvMain" runat="server" AsyncRendering="true" ProcessingMode="Remote" ShowParameterPrompts="False" OnPageNavigation="rvMain_PageNavigation" Width="100%" Height="800px" DocumentMapCollapsed="False" DocumentMapWidth="300px">
</rsweb:ReportViewer>
Hi all,
I'm working on a new SSRS implementation, and have created a Report Model against an SSAS database. I launced Report Builder and can see and select the model in the list, but the OK button at the bottom of the pane is greyed out so I'm unable to actually start building a report against it. Any ideas on what I need to change to make the button work? I have already verified that I have full access to the data source as well as the model, in addition to the underlying SSAS DB, so I don't believe it is a permissions issue... But I'm certainly open to any suggestions.
Thanks!
I'm developing my first Analysis Server report in SRS. Everything was going well when I discovered the filters & parameters pane was missing in the MDX Query Designer in data mode. I have my columns and rows but need to drag a dimension into the filters & parameters pane in MDX Query Designer and set a filter and parameter on it. Any idea how to make it viewable?
View 1 Replies View RelatedI have this strange issue.
VS2005
SSRS2005
SSAS2000 cube based on data from SQL 2005 database
When i connect to the SSAS cube and create a dataset in a SSRS 2005 report i do not get a parameter pane.
Also, if i right click on a dimension & select "Add to filter" i get the following error message
"Object reference not set to an instance of an object"
It's not my installation of SQL 2005, as I can connect to an SSAS2005 cube and get the filter/parameter pane and do not get the error message. Parameters work fine.
Have i come across a known issue when reporting on SQL 2005 versus a SQL 2000 AS Cube... or can someone shed some light on the issue for me....
If someone can tell me how to display screenshots I can add them to a post
Thanks
Will
I have to connect 6 entities. I am getting NO RESULT (no output)!!!
BEGIN TRANSACTION;
DROP TABLE TOPIC;
DROP TABLE LECTURE;
DROP TABLE PRICE;
DROP TABLE COURSE;
DROP TABLE PUPIL;
DROP TABLE LP;
[Code] ....
Hi,
A Composite entity can be made out of two Primary Keys (PK) of two different entities. Therefore it can be called as a link table. Therefore the composite entity depends upon the other two entites. Therefore the composite entity is a exsitence dependent on the other two entities.
Therefore do every composite is a weak entity?
TY
Dear GroupCan anyone provide a sample query for the following scenario?Let's assume I want to search for an order someone placed which mightbe an individual or company. An individuals first name is stored incolumn FirstName And the individuls last name in column LastName of thecontact table and the company name is stored in column CompanyName ofthe company table.If a user issues a query with CName parameter how can I search allthese columns and tables at the same time to see if there's a match? Imight add that there's a column in the orders table that holds0=Contact 1=Company but ideally I don't want to use this column.Thanks very much for your help and efforts.Martin
View 2 Replies View RelatedHello --Is there a way to configure 2000 so it will save a view layout after Ichange it in the diagram pane?Thanks for any help.Larry Mehl
View 2 Replies View RelatedI have VS2008 Express and I am using Sql Server Compact Edition 3.5 which is distributed with it.
As I experienced, there is no possibility to use this database from Sql Server Management Studio (because it is version 3.5 and Management Studio demands version 3.1).
So, the only possibility to design a query is to use the built in Query Builder in Visual Studio Express. This query builder does not save the Diagram Pane content layout. I mean, when a query has 10 tables, I place these tables in the Diagram Pane in some order that makes sense (e.g. to make all relations visible). After saving and reopening the query in Query Builder, all tables are on the left side, in a complete disorder.
Is it possible to somehow save the Diagram Pane content along with the SQL ??
Hi Everyone,
I'm attempting to design a schema for a project I working on. The idea is that I can store a list of documents and associate meta data with them.
The problem is that I don't know what that meta data will be at design time. The user will create the meta data fields in the software. For the first design I just had a table called meta field that contained the meta field defintion, and another table called itemdata that contained the document primary key, the metafield primary key and a value encoded as a varchar. This works fine while the list of documents is small.
Another idea I've thought of is to have seperate tables for all of the metafields. This would allow me to be very specific about the values that can be stored in that column etc. It also turns out that for the kind of queries I'm doing its about 4 times as fast. The only problem is that the software will need to create these tables at run time.
What I would like to know is, is this a really bad idea? If so why?. Also are there any other ways I can store this kind of data?
The kind of queries I am doing are find all the documents where metafield 1 is A and metafield 2 is B and metafield 3 is C and metafield 4 is D etc.
Cheers,
Mark
Our customer (of our ecommerce system) wants to be able to preservedeleted entities in the database so that they can do reporting,auditing etc.The system is quite complex where each end user can belong to multipleinstitutional affiliations (which can purchase on behalf of the user).The end user also has a rich trail of past transactions affiliationsetc. Thus in the schema each user entity is related to many otherswhich in turn relate to yet others and so on.In the past when a user was deleted all of his complex relationshipswere also deleted in a cascading fashion. But now the customer wantsus to add a "deleted" flag to each user so that a user is never_really_ deleted but instead his "deleted" flag is set to true. Thesystem subsequently behaves as if the user did not exist but thecustomer can still do reports on deleted users.I pointed out that it is not as simple as that because the user entityis related to many, many others so we would have to add this "deleted"flag to every relationship and every other entity and thus have"deleted" past purchases, "deleted" affiliations - a whole shadowschema full of such ghost entities. This would overtime degradeperformance since now each query in the system has to add a clause:"where deleted = 0".I assume this is a standard problem since many organizations must havethis need of preserving deleted records (for legal or other reasons).I tried to talk them into creating a simple audit file where all thedeletions will be recorded in XML but they were not too happy withthat.Is there a more satisfying solution to this than have this "deleted"flag?Thanks for your help,- robert
View 9 Replies View RelatedHi everybody,
When I've deployed a report model,in Report Builder I have 4 different entities at the same level,when I drag and drop the first field from one entity after I can't see the other three entities....What did I do wrong??
I don't understand ,if you could help me it would be great.
Have a nice day!
sandy
I have the following data stored in a column in a table:
£10,000.00 & ' " % < > Guðmundsdóttir Björk LÃ¥rs Marqués MarÃa-Jose Carreño Quiñones
I query this in a stored procedure using the FOR XML clause and universal table, and store the result in an XML data type variable:
DECLARE@ResultXML
EXECUTE[someStoredProcedure] @Result OUTPUT
This data (as XML) is then written to a file; in order to do this, I CONVERT the data to NVARCHAR since there are unicode characters in the source:
DECLARE@strResultNVARCHAR(MAX)
SET@strResult= CONVERT( NVARCHAR( MAX ), @Result, 1 )
Now this works fine, except on inspection, SQLServer has decided to render the data thus:
£10,000.00 & ' " % < > Guðmundsdóttir Björk LÃ¥rs Marqués MarÃa-Jose Carreño Quiñones
Why it has changed the apos and quot entities to the corresponding character but not the other entities is beyond me.
how to preserve XML entities?
I have a database that tracks players for children's sports clubs. I haveincluded representative DDL for this database at the end of this post.A single instance of this database supports multiple clubs. I would like toadd support for letting each club define and store custom information aboutarbitrary entities. Basically, allows the clubs to define custom entities(i.e tables) and associated custom attributes (i.e. fields) that may berelated to existing tables (such as Player and FootballClub) or existingentities. For instance, a club may define a PlayerAssessment entity thatrecords all player assessments.To do this, I plan to support the following use case:1. FootballClub admin creates a new entity and gives it a name anddescription (Entity is only accessible to this FootballClub).2. FootballClub admin indicates that the new entity has a M:1 relationshipwith the Player table (this will add Player_ID as a FK attribute).- {An entity may have no relationships.}- {Relationships are also supported to other entities.}3. FootballClub admin specifies the names and domain/types of any dataattributes (i.e. fields) of the entity.- {An attribute's type may be constrained to a few allowable types likeRelationship, Integer, Float, Currency, Date, Time, DateTime, Name,Description and Memo.}4. System creates entity as specified.A few constraints:1. Any entity defined is "private" to the defining club. Other clubs aren'taware of it although they may define custom entities of their ownwith the same name and attributes. [Perhaps there is a way to sharedefinitions of identical entities?]2. A club doesn't have to define any custom entities.Ideas I've considered:1. Generate DLL and create actual tables- Restrict such customizations such that while admin is setting up entities,no other user is allowed to use the system.- Once entity definition is complete, generate an actual table using DLL.Table and column names might be changed to enforce uniqueness/validityconstraints - this suggests a need for table/column name mapping.- PROS: Easy to implement.- CONS: Doesn't scale since only a limited number of tables can be created.DDL on a live, shared system?. Scary!!All users for all clubs will be locked out while entity iscreated.2. Generate DDL and create actual tables in secondary database(s)- Same as above except that the user tables are created in secondary [,shared] databases.- PROS: Reassurance that DDL is never run on the "core" dataAll users don't have to be locked out.- CONS: Doesn't scale since only a limited number of tables can be created.{ Unless I start creating additional databases too!. }Still needs to DDL on a live, shared system.Has anyone done anything similar?. Any ideas on how it might be done?. Inparticular, is this possible without having to execute DDL on the livedatabase?Kunle=================== BEGIN DDL ===================CREATE TABLE FootballClub (Club_ID int IDENTITY,Name char(80) NOT NULL,Area char(4) NOT NULL,League char(4) NOT NULL,City char(30) NOT NULL,PRIMARY KEY (Club_ID))goexec sp_primarykey FootballClub,Club_IDgoCREATE TABLE Player (Player_ID int IDENTITY,First_Name char(30) NOT NULL,Initials char(30) NULL,Last_Name char(30) NOT NULL,Date_Of_Birth datetime NOT NULL,Position char(4) NULL,Club_ID int NULL,PRIMARY KEY (Player_ID),FOREIGN KEY (Club_ID)REFERENCES FootballClub)goexec sp_primarykey Player,Player_IDgoCREATE TABLE UserAccount (User_ID int IDENTITY,Club_ID int NOT NULL,FullName char(80) NOT NULL,Logon char(20) NOT NULL,PWD_Hash char(60) NOT NULL,PRIMARY KEY (User_ID, Club_ID),FOREIGN KEY (Club_ID)REFERENCES FootballClub)goexec sp_primarykey UserAccount,User_ID,Club_IDgoexec sp_foreignkey Player, FootballClub,Club_IDgoexec sp_foreignkey UserAccount, FootballClub,Club_IDgo=================== END DDL ===================
View 3 Replies View RelatedI have configured master data services in my DB server and when i am going to explorer the entities and respected data by using navigating as below
[URL] -->Click to ExplorerÂ

I am able to select  data for only one default entity, in entities drop down is getting hidden back side , please find below snap to clearance.
Â
Note: I am using Internet explorer (11.0.9600.17239) to browse the solution.
I have scoured the internet looking for someone who has run into this issue with no such luck!
Product: Reporting Services 2005 Analysis Services 2005
Our users use Report Builder as an ad-hoc report tool. Data sources for Report Builder come from Report Servers and Analysis Servers.
I have run into issues where a user will select a source for their report that is based on an AS cube. When they select an attribute (field) from a selected entity in the Report Builder Explorer and drop it on the canvas, all entities except the one the field was selected from disappear.
For example:
A cube has a fact table called "Annual Income" with dimension of employee and demographics. In Report Builder, the entities would be listed in the left hand Report Builder Explorer window as
Annual Income
Employee
Demographics
If I select attributes from Employee or Annual Income, as usual, you will see the related hierarchies of the remaining entities. However, if I select an attribute from Demographics, then the Annual Income and Employee entities just disappear and all I can see is the Demographics entity and its attributes.
Has anyone run into this and if so, any ideas? This is becoming extremely frustrating. I have deleted and rebuild cubes, individual dimensions, and everything else I can think of.
Your responses are appreciated,
Scott
After opening the Results Pane (though a query or open table); I frequently get the below message.
Is there any way to change the settings so the results pane will stay active for a long length of time?
Message
SQL Server Enterprise Manager
_______________________
Some time ago, you retrieved data from the database with the query or view whose name is XXXX. The returned rows appear in the Results Pane and the database continues to retain the result set in its local memory; which consumes valuable server resources. Because you have not recently used the Results pane, it will be AUTOMATICALLY CLEARED IN ONE MINUTE. The will empty the results pane, discard unsaved changes, and free resources on the database server. Then if you want to REESTABLISH the result set, you can rerun the query or view.
DO YOU WANT TO PROLONG YOUR WORK WITH THE RESULTS PANE AND CONTINUE TO CONSUME RESOURCES ON THE DATABASE SERVER?
--------------------------------------------------------------------
I want this to default to Yes, without this message comming up and if I don't click yes fast enough, my results are gone. I kinow the work arounds, but how can I default this to Yes or so this message stops displaying.
Thank you
watxnd@hotmail.com
Let's say I have an rdlc report for customers, and it has three sections with subreports:
CUSTOMER_INFO (name, phone, e-mail)
ITEMS
OBSERVATIONS
As the page only has 21cm and the report grows horizontally, I need to repeat the WHOLE report with all of its sections after every 4 customers. So if I have 5 customers, there would be three pages(let's say every subreport takes 1 page) for the first four customers, and then another three pages(with every section once again) just for one customer.
Is it possible? It doesn't seem somehing out of the ordinary, but I can't figure how to make it work. Everytime there is more than 4 customers, the report breaks its layout trying to accommodate the records in a new table below the tables of each subreport.
The expected result would be to bring all of exceeding data to new pages.
I am trying the assign the models to the user in the user management page. But when I try to expand the + symbol next to the model and open the entities, I get the error that the object reference is not set for a object server instance.
View 2 Replies View RelatedHello:
I am still working on my project, but figured I would share my findings with the community, on how I imported my SQL Server database tables into a new SQL Compact Edition database. Unfortunately, I haven't figured a workaround to the IDENTITY_INSERT issue, but other than that, I will proceed.
1) I downloaded and played with the tool presented in the article "Generate SQL Insert statements for your SQL Server 2000 Database" I made the following modifications . (my modification can be found here)
a) Disable scripting dependencies
b) Add a filter for objects to script
c) Prompt for each table from which to export data
2) From the scripts output by this tool, I had to (which I may integrate into the tool later... who knows)
a) Remove 'On Primary' statements after the create table statements.
b) Remove date defaults.
c) Most other SQL generated by the tool seemed to work.
3) Execute each 'Create Table' statement individually.
4) For the Data Import, I had to add semi-colons to the end of each Insert Line.
5) I Have found no workaround for the IDENTITY_INSERT issue. If anyone has a good idea, I would love to hear it. In my case, I was importing the DotNetNuke core store module tables populated with about 2500 products and 20+ categories consisting of a simple categories table and products table. I imported the categories table into a fresh MS Access database file, and then ran the update query against the attached products table in the original SQL Database, then regenerated the insert statements, and imported the data generating fresh product ID's. This database will only be read only, so it didn't matter about maintaining the Product ID's, just the relationship between the products and categories.
Separately, I found a great blog article on how to embed a SQL script as a resource for your application and run it on your applications first run, so my steps above would provide a quick foundation for generating that sql script.
I'm new to my company, although not new to SQL 2005 and I found something interesting. I don't have an ERD yet, and so I was asking a co-worker what table some data was in, they told me a table that is NOT in SQL Server 2005's list of tables, views or synonyms.
I thought that was strange, and so I searched over and over again and still I couldn't find it. Then I did a select statement the table that Access thinks exists and SQL Server does not show and to my shock, the select statement pulled in data!
So how did this happen? How can I find the object in SSMS folder listing of tables/views or whatever and what am I overlooking?
Thanks,
Keith
We have 20 -30 normalized tables in our dartabase . Also we have 4tables where we store the calculated data fron those normalised tables.The Reason we have these 4 denormalised tables is when we try to dothe calcultion on the fly, our site becomes very slow. So We haveprecalculated and stored it in 4 tables.The Process we use to do the precalcultion, will get do thecalculation and and store it in a temp table. It will compare the thetemp with denormalised tables and insert new rows , delte the old oneans update if any changes.This process take about 20 mins - 60mins. Ittakes long time because in this process we first do the calculationregardless of changes and then do a compare to see what are changed andremove if any rows are deleted, and insert new rowsand update thechanges.Now we like to capture the rows/columns changed in the normalisedtables and do only those chages to the denormalised table , which weare hoping will reduce the processing time by atleast 50%WE have upgraded to SQL SERVER 2005.So We like to use the newtechnology for this process.I have to design the a model to capture the changes and updated onlythose changes.I have the list of normalised tables and te columns which will affectthe end results.I thought of using Triggers or OUTPUT clause to capture the changes.Please help me with the any ideas how to design the new process
View 3 Replies View RelatedI am using the following select statement to get the row count from SQL linked server table.
SELECT Count(*) FROM OPENQUERY (CMSPROD, 'Select * From MHDLIB.MHSERV0P')
MHDLIB is the library name in IBM DB2 database. The above query gives me only the row count of table MHSERV0P. However, I need to get the names, rowcounts, and sizes of all tables that exist in MHDLIB librray. Is it possible at all?
From Newbie to Newbie,
Add reference to:
'Microsoft ActiveX Data Objects 2.8 Library
'Microsoft ADO Ext.2.8 for DDL and Security
'Microsoft Jet and Replication Objects 2.6 Library
--------------------------------------------------------
Imports System.IO
Imports System.IO.File
Code Snippet
'BACKUP DATABASE
Public Shared Sub Restart()
End Sub
'You have to have a BackUps folder included into your release!
Private Sub BackUpDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BackUpDB.Click
Dim addtimestamp As String
Dim f As String
Dim z As String
Dim g As String
Dim Dialogbox1 As New Backupinfo
addtimestamp = Format(Now(), "_MMddyy_HHmm")
z = "C:Program FilesVSoftAppMissNewAppDB.mdb"
g = addtimestamp + ".mdb"
'Add timestamp and .mdb endging to NewAppDB
f = "C:Program FilesVSoftAppMissBackUpsNewAppDB" & g & ""
Try
File.Copy(z, f)
Catch ex As System.Exception
System.Windows.Forms.MessageBox.Show(ex.Message)
End Try
MsgBox("Backup completed succesfully.")
If Dialogbox1.ShowDialog = Windows.Forms.DialogResult.OK Then
End If
End Sub
Code Snippet
'RESTORE DATABASE
Private Sub RestoreDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
RestoreDB.Click
Dim Filename As String
Dim Restart1 As New RestoreRestart
Dim overwrite As Boolean
overwrite = True
Dim xi As String
With OpenFileDialog1
.Filter = "Database files (*.mdb)|*.mdb|" & "All files|*.*"
If .ShowDialog() = Windows.Forms.DialogResult.OK Then
Filename = .FileName
'Strips restored database from the timestamp
xi = "C:Program FilesVSoftAppMissNewAppDB.mdb"
File.Copy(Filename, xi, overwrite)
End If
End With
'Notify user
MsgBox("Data restored successfully")
Restart()
If Restart1.ShowDialog = Windows.Forms.DialogResult.OK Then
Application.Restart()
End If
End Sub
Code Snippet
'CREATE NEW DATABASE
Private Sub CreateNewDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
CreateNewDB.Click
Dim L As New DatabaseEraseWarning
Dim Cat As ADOX.Catalog
Cat = New ADOX.Catalog
Dim Restart2 As New NewDBRestart
If File.Exists("C:Program FilesVSoftAppMissNewAppDB.mdb") Then
If L.ShowDialog() = Windows.Forms.DialogResult.Cancel Then
Exit Sub
Else
File.Delete("C:Program FilesVSoftAppMissNewAppDB.mdb")
End If
End If
Cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb;
Jet OLEDB:Engine Type=5")
Dim Cn As ADODB.Connection
'Dim Cat As ADOX.Catalog
Dim Tablename As ADOX.Table
'Taylor these according to your need - add so many column as you need.
Dim col As ADOX.Column = New ADOX.Column
Dim col1 As ADOX.Column = New ADOX.Column
Dim col2 As ADOX.Column = New ADOX.Column
Dim col3 As ADOX.Column = New ADOX.Column
Dim col4 As ADOX.Column = New ADOX.Column
Dim col5 As ADOX.Column = New ADOX.Column
Dim col6 As ADOX.Column = New ADOX.Column
Dim col7 As ADOX.Column = New ADOX.Column
Dim col8 As ADOX.Column = New ADOX.Column
Cn = New ADODB.Connection
Cat = New ADOX.Catalog
Tablename = New ADOX.Table
'Open the connection
Cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb;Jet
OLEDB:Engine Type=5")
'Open the Catalog
Cat.ActiveConnection = Cn
'Create the table (you can name it anyway you want)
Tablename.Name = "Table1"
'Taylor according to your need - add so many column as you need. Watch for the DataType!
col.Name = "ID"
col.Type = ADOX.DataTypeEnum.adInteger
col1.Name = "MA"
col1.Type = ADOX.DataTypeEnum.adInteger
col1.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col2.Name = "FName"
col2.Type = ADOX.DataTypeEnum.adVarWChar
col2.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col3.Name = "LName"
col3.Type = ADOX.DataTypeEnum.adVarWChar
col3.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col4.Name = "DOB"
col4.Type = ADOX.DataTypeEnum.adDate
col4.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col5.Name = "Gender"
col5.Type = ADOX.DataTypeEnum.adVarWChar
col5.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col6.Name = "Phone1"
col6.Type = ADOX.DataTypeEnum.adVarWChar
col6.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col7.Name = "Phone2"
col7.Type = ADOX.DataTypeEnum.adVarWChar
col7.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col8.Name = "Notes"
col8.Type = ADOX.DataTypeEnum.adVarWChar
col8.Attributes = ADOX.ColumnAttributesEnum.adColNullable
Tablename.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "ID")
'You have to append all your columns you have created above
Tablename.Columns.Append(col)
Tablename.Columns.Append(col1)
Tablename.Columns.Append(col2)
Tablename.Columns.Append(col3)
Tablename.Columns.Append(col4)
Tablename.Columns.Append(col5)
Tablename.Columns.Append(col6)
Tablename.Columns.Append(col7)
Tablename.Columns.Append(col8)
'Append the newly created table to the Tables Collection
Cat.Tables.Append(Tablename)
'User notification )
MsgBox("A new empty database was created successfully")
'clean up objects
Tablename = Nothing
Cat = Nothing
Cn.Close()
Cn = Nothing
'Restart application
If Restart2.ShowDialog() = Windows.Forms.DialogResult.OK Then
Application.Restart()
End If
End Sub
Code Snippet
'COMPACT DATABASE
Private Sub CompactDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
CompactDB.Click
Dim JRO As JRO.JetEngine
JRO = New JRO.JetEngine
'The first source is the original, the second is the compacted database under an other name.
JRO.CompactDatabase("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program
FilesVSoftAppMissNewAppDB.mdb; Jet OLEDB:Engine Type=5", "Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:Program FilesVSoftAppMissNewAppDBComp.mdb; JetOLEDB:Engine Type=5")
'Original (not compacted database is deleted)
File.Delete("C:Program FilesVSoftAppMissNewAppDB.mdb")
'Compacted database is renamed to the original databas's neme.
Rename("C:Program FilesVSoftAppMissNewAppDBComp.mdb", "C:Program FilesVSoftAppMissNewAppDB.mdb")
'User notification
MsgBox("The database was compacted successfully")
End Sub
End Class