Compact.database

Nov 20, 2007

Here's the Active X Script I'm trying to migrate into an SSIS task:


Function Main()
Dim Engine, dbPath

dbPath = "c:path..."
Set Engine = CreateObject("JRO.JetEngine")

Main = DTSTaskExecResult_Success
Engine.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath &

"database_UnCompress.mdb", _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath &

"testdatabase.mdb"
End Function


I have problems in migrating. Here are the questions:
1. Is the ShrinkDatabase task the SSIS equivalent of this script? If so, I'm having problems creating a new connection since it is an mdb file stored on local computer and not on a server. Is there a workaround on this?
2. If I have to use the script task, I use this codes:

Set Engine = CreateObject("DAO.DBEngine.35")
Engine.CompactDatabase "C:.path...database_UnCompress.mdb", "C:path... estdatabase.mdb"
or

Set Engine = CreateObject("JRO.JetEngine")
Engine.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=pathdatabase.mdb", _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=pathcompacted_database.mdb"
Both produce the same error: Option Strict On disallows late binding. Any suggestions?

3. I also tried to use the Execute SQL Task, using dbcc shrinkdatabase. But I seem lost again in creating a connection. Should I consider "database_UnCompress.mdb" as flat file source?

Any help deeply appreciated. Thanks.

View 9 Replies


ADVERTISEMENT

How I Imported My SQL Database Entities Into My SQL Compact Database

Nov 29, 2006

Hello:

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.

 

View 3 Replies View Related

Database Compact?

Feb 11, 2007

Hi,
I am wondering why a database that was 2Gb in size would come down to just 300Mb when I copy all the data from the origional database into a blank new one.
I ran a database shrink on the origional database but that only reduced it from 2.1 Gb to 2Gb.
I was shocked to see the size difference when I copied the data.
As far as I can tell the copied database still contains all the records of the origional.

I remember that MSAccess had a Compact function that literally copied all the data from one databse into a new empty one - is there the equivalent for SQL?

Any thoughts?

Thanks

View 8 Replies View Related

Compact Database

Jul 20, 2005

Is there a stored procedure that allow the compact database programmaticallyin SQL Server?

View 2 Replies View Related

Compact And Repair Database

Aug 17, 2004

I'm an Access man moving towards SQL Server and I'm interested in knowing if there is a utility for my SQL Server version 7 database that is similar to Access' Compact and Repair utility? I'd like a recommendation from a guru on what Prevenatative Maintenance measures I should be running daily/weekly/monthly etc...

Thanks in advance.
dog

View 3 Replies View Related

Compact And Repair Database ?

Aug 29, 2006

I want to use function "Compact and repair database" in SQL Server2000 which like function Access 2003.
How can i do ?
Thank you very much.

View 2 Replies View Related

Access To A SQL Compact Database With ADO

Sep 28, 2007

I want to access to a SQL Compact database with an application develope with Visual Basic ans using ADO (not ADO .Net). Is it possible and how ?
Thank You.

View 1 Replies View Related

SQL Server Compact Database..

Jan 4, 2008

Hi,

I am new to SQL Mobile developement and trying to developing a small device appliction. My C# code is shown below. My code is executing correctly, but i am not able to see the inserted records in to my SQL Compact database. What i am doing here? Please help.

FYI, I have added the database in to my solution. My solution and database resides in E:\some directory, where as the my assembly code base represents the datasource as \ProgramFilesMobExpTrackerMobExpTracker.sdf.


SqlCeConnection connection

{

get

{

if (_connection == null)

{

_connection = new SqlCeConnection(ConnectionString);

_connection.Open();

}

return _connection;

}

}

string CurrentFolder

{

get {return Path.GetDirectoryName(Assembly.GetExecutingAssembly().GetName().CodeBase);

}

}

const string databaseLocalFileName = "Northwind.sdf";

string DatabasePathName

{

get { return Path.Combine(CurrentFolder, databaseLocalFileName); }

}

const string BaseConnectionString = "Data Source=";

string ConnectionString

{

get { return BaseConnectionString + DatabasePathName; }

}


const string sql =

"INSERT INTO mobexptracker(ExpenseDt,ExpenseDescr,ExpenseAmt) VALUES (@ExpenseDt,@ExpenseDescr,@ExpenseAmt)";

const string sql1 =

"SELECT COUNT(1) FROM mobexptracker";



private void btnSave_Click(object sender, EventArgs e)

{

bool savesucess = Save();

if (savesucess)

{

MessageBox.Show("Expense Saved Sucessfully!");

}

else

{

MessageBox.Show("Error!");

}

}


bool Save ()

{

bool success= false;

using (SqlCeCommand cmd = new SqlCeCommand(sql,connection))

{

cmd.Parameters.Add("@ExpenseDt", txtDate.Text);

cmd.Parameters.Add("@ExpenseDescr",txtExpDesc.Text);

cmd.Parameters.Add("@ExpenseAmt",txtExpAmount.Text );

int cnt = (int)cmd.ExecuteNonQuery(); // ExecuteScalar();

cmd.

success = cnt > 0;

}



return success;

}



View 7 Replies View Related

Compact Sqlce Database

Apr 26, 2007

Hi, there;

I got a SqlCe2.0 database here called JOB.sdf, size is 260K. When I tried to compact it, it took very long and that JOB.sdf.tmp was created with 27.7M!!!, I can still open table in the database, but I found that there is some invalid data (unreadable square boxes). It looks like database was corrupted. My application gave error:

Error Code: 80040E14
Message : The OLE DB Execute method failed. The SQL statement is not valid. [,,,SQL statement,,]
Minor Err.: 28560
Source : Microsoft SQL Server 2000 Windows CE Edition
Error Code: 80040E14
Message : Line 1: Incorrect syntax near 'à Ã§¼‚¬Ã©€™€°Ã¬±´ÃªÅ ®Ã£¶»Ã¤¡Æ’ꢀ¹Ã¨Å¸¬Ã£³Å¡'.
Minor Err.: 170
Source : Microsoft OLE DB Provider for SQL Server
Error Code: 80040E14
Message : Unclosed quotation mark before the character string 'à Ã§¼‚¬Ã©€™€°Ã¬±´ÃªÅ ®Ã£¶»Ã¤¡Æ’ꢀ¹Ã¨Å¸¬Ã£³Å¡'.
Minor Err.: 105
Source : Microsoft OLE DB Provider for SQL Server</E>



Note: " " is displayed as "SOHSOH....." with black background.



Apart from compacting database, is there manual job or tool we can do/use to fix a corrupted database caused by power failure? (e.x: all data saved successfully before power failure)?



Thanks

View 6 Replies View Related

Compact Sql Mobile 3.0 DataBase In C++/OLE DB ?

Jan 2, 2006

Hello,

I'm porting my C++ (EVC3) application from Sql CE 2.0 to Sql Mobile 3.0
(with OLE DB). To compact a 2.0 database, i used ISSCECompact interface.
But it doesn't exists with the new version. I've only found C#
and VB.Net samples in MSDN ...

Thanks in advance.

View 8 Replies View Related

Zero Out SQL Server Compact 3.5 Database

Feb 22, 2008



I have an application that uses a SQL Server Compact 3.5 database backend. Everytime the application starts I want to zero out my database, meaning remove all records and reseed all identities. This datatbase is then filled based on a directory structure. Once this DB is filled I then send the entire database to a Windows Mobile PPC device via 802.11g WIFI. Before sending the database I first create an MD5 checksum on both the server and client sides to see if the database actually needs to be transfered. If the checksums match I will not send it as the client already has the lastest copy.

My problem is that I can never count on the database identity values starting at the same number on any particular instance of the application so the checksums are usually different. To solve this problem I need to ensure that everytime I start my server application the database (and all identifiers) will always be the same, will always be as if I just creaed it. How do I do this?!?!?!? This seems like it would be a trivial operation as I can imagine its use in multiple situations. Anyone know how to zero out the database?

View 1 Replies View Related

How To Connect To Compact Edition Database

Dec 18, 2007

I need to create a desktop program on vb.net , based SQL compact edition. but I do not know how to connect to CE database. I already create the sdf file, but I can not connect it from the poject , is there any way to do it ?

View 1 Replies View Related

Compact Database Error Code: 0

Jan 9, 2006

I am currently working in VB.NET with SQL Mobile 2005.  I am trying to compact a database, .sdf, using the SqlCeEngine Object compact(connectionstring).  When I use this code to compact a small database, only about 300KB, it returns okay however when I try to compact a larger database, about 9MB, it returns with a SQL Error Code: 0, "The operation completed successfully" however with no compacted database.  The code works however it only seems to work for smaller databases.  Does anyone have any ideas on how to correct this error or tell me why it returns with that type of error.

Snuggs

View 8 Replies View Related

SQL Server 2005 Express - Compact Database ?

Mar 3, 2007

Every time I modify my SQL Server database, it grows by about 1 MB.  I haven't added any data, in fact have reduced column sizes. Is there a utility that will compact the database and return it to a more manageable size? 

View 2 Replies View Related

Creating Database Diagrams With SQL Compact 3.5 And VS2008 RTM

Nov 30, 2007

I'm using VS2008 RTM with a new SQL Compact 3.5 database and I read in VS2008 and SQL Compact 3.5 BOL that Database Diagrams are supported as well as Foreign Key creation from Server Explorer.

However, there doesn't seem to be support for this. Database Diagram commands are not displayed for this new database (but it is for my SQL Express database) as instructed by BOL.

Can anyone tell me if VS2008 fully supports SQL Compact 3.5 with database diagrams and visual tools to create relationships? If so, how can you create a database diagram in VS2008 for SQL Compact 3.5?

View 1 Replies View Related

Designing A Compact Database For Mobile Device

Apr 4, 2008

Hey guys,

So I started an SQL CE database for use inside a mobile application. I used SSMS to create a .sdf file (because if I let Visual Studio do it, SSMS can't open it because it will be version 3.5).

The reason I wanted to use SSMS is because I wanted to be able to design the database in design view, and populate it with initial information for the application to use. That means adding tables and rows.

The problem is, SSMS doesn't seem to have a design view available when working with CE databases. It also doesn't seem to have a feature for adding rows into tables. It's essentially as useless as VS2008 for designing my mobile database. It only lets me add tables, and I can't even do that visually in design view. I have to use those cumbersome forms.

Is there any way to design a CE database in design view? And add rows of data into tables? It feels like I'm overlooking something.

Note: I dont need to subscribe or publish the database. The mobile application just needs to use the database as a repository.

View 7 Replies View Related

Compact Edition Database In Object Store On Windows CE 4.2

Oct 10, 2007

The device I'm working on has a filesystem on compact flash, but we're concerned about burning it up with excessive writes. One solution might be to keep the SQL database in the the object store, perhaps dumping a copy out to the CF as backup. Any pointers as to how to accomplish this? If we were to keep the db in a file on the CF, what's the SQL Server CE behavior for writing to the file? Does it write every time a record is created/updated, or is there any ability to control when the data is flushed to the file?

Thanks from an SQL newbie.

View 2 Replies View Related

Compact Server Database-file Not Enabled For Replication

Apr 23, 2008

When I try to create a subscription to my SQL Server Compact 3.5-database file, it gives an SqlCeException-message that says that the file is not enabled for replication. How do enable it?

My SQL Server Management Studio won´t connect to my compact server file right now, so that method is not an option for me right now.

View 1 Replies View Related

SQL SERVER 2005 COMPACT EDITION RC1 - Where To Find Editor For .SDF Database?

Dec 11, 2006

We have a plan to use Microsoft SQL Server 2005 Compact Edition RC1 as a backend. Our Front-end is VS .NET (VB.NET).

We want to know how to edit the Compact Edition database (.SDF). Is there any editor is available right now in the market? or microsoft provided any editor for this.

OR Can i use microsoft 2005 sql server standard edition for this.



Thanks

View 5 Replies View Related

Convert Access Database To Sql Server 2005 Compact Edition

Nov 7, 2007



Is there any way to convert an Access or SQL server 2005 database to SQL server 2005 compact edition database??



Thank you very much!!

View 8 Replies View Related

View Data From A Compact Database With The Management Studio Express ?

Jan 23, 2007

Hi there, after some hours of installing and experiments i can now open and sql server compact database (sdf) iam also able to create a new database and add tables and columns. (everything with the ms sql server management studio express)

i can also open the northwind database. but iam not able to view the data .

if i rightclick on a normal database table i can choose ->open table and see its content.

but on a compact table i have at rightclick only

edit table
properties
delete

refresh



how can i see the data in the table or add new content ?

thanks

View 8 Replies View Related

SQL Server Compact Edition Encountered Problems When Opening The Database

Oct 10, 2007

I'll try and ask one more time. Does anyone have a clue how to fix this?

"A SQL Server Compact Edition DLL could not be loaded. Reinstall SQL Server Compact Edition. [ DLL Name = sqlceca30.dll ]"

or

"SQL Server Compact Edition encountered problems when opening the database"
"Internal error: The database is not initialized."


I've been fighting this for well over a week now. My XP based version works fine but this error occurs routinely on my CE4.2 device. I cannot find anything meaningful using Google other than 'look for non disposed' replication objects which I've done. Is this bad hardware? I don't believe I'm out of memory as the app is about 1.2MB including about 8 DLL's and GC indicates I have about 300KB of objects at the time of the failure. I also use a bunch of static (global) objects that I use throughout the code.

It would be nice if I could get the lower level error messages causing these failures but that is probably too much to ask.

The odd thing is that it's only the replication engine that fails as even after the failure, the app can reopen the DB and work with the data just fine.

My basic order of the application is...

1 Open DB SqlCEConnection
2 Create/Prepare any SqlCECommand objects
3 Do work here
4 Dispose any SqlCECommand objects
5 Close DB
6 Dispose SqlCEConnection object
7 Create SqlCEReplication object
8 BeginSynchronize
9 ... error here..
10 Dispose SqlCEReplication object
11 Reopen DB SqlCEConnection


I've tried creating the Replication object at program start and that does not help. I'm guessing there is something about .net that I don't understand either with stack space, # dll's or whatever. I've rearranged the order of the Dispose, Create, SQL CE objects to include just about every permutation but still am hitting this same problem.

Are there any tools that can help me profile this app for memory problems or usage? Everything seems geared for .Net and not .NetCF.

thanks.

View 4 Replies View Related

How Can I Get Identitiy Field From Database While Inserting New Row In Sql Server 2005 Compact Edition

Aug 2, 2007



How Can I get Identitiy field from database while inserting new row in sql server 2005 compact edition.

Ex:

I am inserting row in a table through SqlQuery ("insert into ....") in which one of the field is of type Identity which generates number automatically. I want that number to pick up that number and used it in child table....


Any solution for it.


View 10 Replies View Related

How To Get A Microsoft SQL Server Compact 3.5 Database Multiuser Work On A Network Share

May 21, 2008

Hello All,

I need some advise/help how to deploy a small sql server compact 3.5 database on networkshare where about 8 people have access to it(install de client ) and can modify the database with aprox 3000 records.
I'm using visual basic 2008 and created a compact database and filled it with one table of an export of an access database done with data port console.
And created a new windows forms application and saved the project.
Publish is not the problem but how too set it up......
There is not much info to find on how to get a sql server 3.5 database running on a network share.
Who can help ???


Thanks


View 2 Replies View Related

Sync/Merge Local Sql Compact Databases To Single Global Database

Jun 23, 2007

Hi, I have compact sql databases which will be local on multiple users
devices. Due to space constraints, for one of the tables i have had to use
auto incrementing integer which works fine for the local database but i
would like to merge all of the users databases into a global database. The
table format can be seen below:




Code SnippetCREATE TABLE Players
(
ID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
FirstName NVARCHAR(32),
LastName NVARCHAR(32)
);

CREATE TABLE Sessions
(
ID INTEGER NOT NULL IDENTITY,
PlayerID UNIQUEIDENTIFIER ,
SessionDateTime DATETIME,
CONSTRAINT pkSessions PRIMARY KEY (ID),
CONSTRAINT fkPlayerID FOREIGN KEY (PlayerID) REFERENCES Players(ID)
);

CREATE TABLE SessionDetail
(
SessionID INTEGER,
Time real,
Power real,
CONSTRAINT pkSessionDetail PRIMARY KEY (SessionID,StrokeTime),
CONSTRAINT fkSessionID FOREIGN KEY (SessionID) REFERENCES Sessions(ID)
);



The Players table will merge fine as GUIDs are used. However, how will the
sync capabilities of compact SQL handle the sessions table? When it pushes
the local data to the remote database will it change the Sessions.ID column
to a unique field (as no doubt lots of people will have 1, 2, 3 in their
local databases and the global database must have a unique ID), and then
transfer this changed ID back to the local database? Furthermore, if it
changes the Sessions.ID column during the merge it will also need to update
the SessionDetail.SessionID foreign key to maintain referential integrity,
is this also handled?

So my question is, how much of this sync and data merge is automated and are
there any good examples or pointers for my scenario? I cannot reasonably use
a GUID for Session.ID as there will be lots of SessionDetail entries and the
size would be far too much.

Many thanks,

Chris

View 7 Replies View Related

Trying To Connect To Northwind Database, On SQL Compact Server 3.5, An Error Generated, 'The Event Log Is Full'

Dec 26, 2007

View 1 Replies View Related

How To Connect ADODB With Microsoft SQL Server Compact 3.5 (.NET Framework Data Provider For Microsoft SQL Server Compact 3.5)

Sep 12, 2007

Hi
We are checking VB 9 (Orcas).

we connected to database created under with sql server 7. with this code

Public cn As New ADODB.Connection

Public Sub OpenDB()


cn.Open("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial catalog=Reservation;Data Source=.")

End Sub
this code worked well.
we know sql7 is not compatiable with vista. please tell us how to connect it wiith sql2005 . we downloaded orcas express edition beta. we created a database also. please let u know how to connect with Microsoft SQL Server Compact 3.5 (.NET Framework Data Provider for Microsoft SQL Server Compact 3.5).

Rgds
Pramod

View 7 Replies View Related

Sqlceoledb35.dll - Error 25123 - A SQL Server Compact Edition DLL Could Not Be Loaded. Reinstall SQL Server Compact Edition.

May 5, 2008

Hello,

we've got a SQL Server 2005 which replicates with an SQL Server Compact 3.5. Every 10 to 20 synchronisations we're getting the error mentioned above. A Soft reset of the device helps to make the synchronization working again.

Why is this error happening and how can we resolve this?

BTW, we've also running system that replicates with an SQL Server Compact 3.0 without having this problems.

Thanks,
Markus

View 1 Replies View Related

Solution!-Create Access/Jet DB, Tables, Delete Tables, Compact Database

Feb 5, 2007

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

View 1 Replies View Related

How To Convert SLQ Server Express Database File To SQL Compact Edition File?

May 29, 2007

Hi all,



I have a database name MyDatabase (SQL Server Express Dabase File). Is there anyway that I could convert it to SQL Server Compact Edition File?



By the way does anyone here got any problem with programming in SQL Server Compact Edition? It troubles me.



Thanks,

bombie

View 6 Replies View Related

Does Sql Server Compact Edition 3.5 Support RDA Synchronization With Sql Server 2000 Database?

Jan 21, 2008

Does Sql Server Compact Edition 3.5 support RDA synchronization with Sql Server 2000 database?

View 4 Replies View Related

Error While Connecting To SQL Server Compact 3.5 Database With SQL Server Management

Mar 11, 2008

Hello,

i tried to connect to a SQL Server Compact Database (version 3.5) with SQL Server Management Studio Express (Version 9.00.3042.00).

But i get the follwing error message (sorry, it's a german message):

.....................................
Es kann keine Verbindung mit 'D:DocumentsVisual Studio 2008Projects\_ProduktivSuperkalibrator_PrototypenSuperkalibrator_PrototypenDatenbankDatabaseTest.sdf' hergestellt werden.

------------------------------
ZUSÄTZLICHE INFORMATIONEN:

Sie versuchen, auf eine ältere Version einer Datenbank von SQL Server Compact Edition zuzugreifen. Falls es sich um eine Datenbank von SQL Server CE 1.0 oder SQL Server CE 2.0 handelt, führen Sie 'upgrade.exe' aus. Falls es sich um eine Datenbank von SQL Server Compact Edition 3.0 oder höher handelt, führen Sie die Komprimierung und Reparatur aus. [ Db version = 3505053,Requested version = 3004180,File name = D:DocumentsVisual Studio 2008Projects\_ProduktivSuperkalibrator_PrototypenSuperkalibrator_PrototypenDatenbankDatabaseTest.sdf ] (SQL Server Compact Edition ADO.NET Data Provider)
.....................................

Is there a solution to manage Compact Databases in Version 3.5?

Thanks!
Stefan Wagner

View 4 Replies View Related

A Sql Server Compact Edition DLL Could Ot Be Loaded Reinstall SQL Server Compact Edition [ DLL Name = Sqlceca30.dll]

Mar 11, 2008



Hi there,

everything is ok for first run. but i leave the program relogin
than that error occured ppc2003 second edition devices. Windows mobile 5.0 device works fine.
can anybody help me?


VS2005 ver 8.0.50727
SSCE31VSTools-ENU.exe loaded
SSCE31SDK-ENU.msi loaded

machine 1
SQL 2005 loaded

machine 2
http://192.168.20.22/ssce/sqlcesa30.dll
"Microsoft SQL Server Compact Edition Server Agent" looks fine

pocket pc side
C:Program FilesMicrosoft SQL Server Compact Editionv3.1SDKinwce400armv4

.net cf 2.0 sp2
sqlce30.dev.ENU.ppc.wce4.armv4.CAB
sqlce30.ppc.wce4.armv4.CAB
sqlce30.repl.ppc.wce4.armv4.CAB installed too.

my code
---------------------------------------------

Dim RdaStr As String = "Provider=SQLOLEDB; Data Source=" + Server + ";Initial Catalog=" + DataBase + ";Integrated Security=SSPI"

Dim rda As SqlCeRemoteDataAccess

Try

rda = New SqlCeRemoteDataAccess "THIS LINE GIVES ME THAT ERROR
Catch ex As Exception

MsgBox(ex.ToString)

Application.Exit()

End Try

Try

rda.InternetLogin = [String].Empty

rda.InternetPassword = [String].Empty

rda.InternetUrl = "http://" + IP_no + "/ssce/sqlcesa30.dll"

rda.LocalConnectionString = ConnectString

Catch ex As Exception

MsgBox("Bağlantı hatası..")

Application.Exit()

End Try
------------------------------------------------------------------------------
i added the following code to very beginning of my code too.
that code lock my device


Declare Function LoadLibrary Lib "coredll" Alias "LoadLibrary" (ByVal lpLibFileName As String) As IntPtr


Dim pt As IntPtr

pt = LoadLibrary("\windowssqlceca30.dll")

pt = LoadLibrary("\windowssqlceoledb30.dll")

pt = LoadLibrary(\windowssqlcecompact30.dll)


-----------------------------------------------------------------------------

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved