[EndLessMind]

Hi.
In one of my programs I have a MS Access database that i want to change so i can access it from a FTP server.
however i have read that that's not possible.
But can i use a SQL on a FTP server or is there an other solution?

Thanks in advance

[motcom]

Hi EndlessMind,

I deffinitely would not use access on any public server...
Most hosting firms/companies will give you either a MySQL or a MSSQL server - since you are talking about FTP here i would think a Http is there as well. MySQL can run on linux or Windows whereas MSSQL only on Windows (at least thats what i know off and I havent seen anyone use it on linux)

If it is either MySQL or MSSQL, some of your querries would have to change a little bit, not a lot.

But you would not host it on a FTP server but its a Normal SQL database hosted on the server. Thus with the correct username and password, you can connect to that database no problem. I just hope you have a fast interet connection when its a windows app that runs on a client machine and then connects to the SQL database on the web server. If its an ASP web site, you normally host that on the Web Server as well, then you should not have a problem.

Just a note on Access, it does have connection limits and is not that secure.

For me access is just a simple program/database that i would recommend my dad to use for his own internal stuff in the building industry, as soon as a access database becomes bigger (at about 60MB i found) it starts to cause issues. I don't even want to think about corruption on the database (it happened a lot at my previous firm i was working at)

Hope this helps...

Motcom

[EndLessMind]

motcom, on 10 March 2010 - 10:37 PM, said:

Hi EndlessMind,

I deffinitely would not use access on any public server...
Most hosting firms/companies will give you either a MySQL or a MSSQL server - since you are talking about FTP here i would think a Http is there as well. MySQL can run on linux or Windows whereas MSSQL only on Windows (at least thats what i know off and I havent seen anyone use it on linux)

If it is either MySQL or MSSQL, some of your querries would have to change a little bit, not a lot.

But you would not host it on a FTP server but its a Normal SQL database hosted on the server. Thus with the correct username and password, you can connect to that database no problem. I just hope you have a fast interet connection when its a windows app that runs on a client machine and then connects to the SQL database on the web server. If its an ASP web site, you normally host that on the Web Server as well, then you should not have a problem.

Just a note on Access, it does have connection limits and is not that secure.

For me access is just a simple program/database that i would recommend my dad to use for his own internal stuff in the building industry, as soon as a access database becomes bigger (at about 60MB i found) it starts to cause issues. I don't even want to think about corruption on the database (it happened a lot at my previous firm i was working at)

Hope this helps...

Motcom

Hi.
Okey, yes, i have a fast Internet connection ( 100mbit fiber ) so that no problem.


Yes it's a web server ( hosted by one.com ) so i was thinking about MsSQL.

What code would i need to change?
Connection string? Navigation code? Search code? Save/update record core?


Thanks in advnace

[motcom]

Hi,

With your querries that include dates... in Access its like

SELECT * FROM Table1 WHERE DueDate = #01-01-2010#

in sql its like

SELECT * FROM Table1 WHERE DueDate = '01-01-2010'

you might want to check your datatypes in SQL as well, there is "Varchar" for strings, "Numerc" and "Int" etc. I can't remember but in access i think its "Text" and "Number".

Do you use datasets or inline querries?

Edit:

Oh yes, your connection string will change aswell, but nothing to hectic, you migh have to setup the dbase on the webserver, hopefully there is a control panel or something like that, but you can get that info from "one.com". They will give you a username and password for the database and that you have to use in your connection string. You can even connect to the dbase via the SQL "Management Console" if you have the IP address (or the servername) and server instance name.

This post has been edited by motcom: 11 March 2010 - 03:34 AM

[EndLessMind]

motcom, on 11 March 2010 - 02:30 AM, said:

Hi,

With your querries that include dates... in Access its like

SELECT * FROM Table1 WHERE DueDate = #01-01-2010#

in sql its like

SELECT * FROM Table1 WHERE DueDate = '01-01-2010'

you might want to check your datatypes in SQL as well, there is "Varchar" for strings, "Numerc" and "Int" etc. I can't remember but in access i think its "Text" and "Number".

Do you use datasets or inline querries?

Hi.
Okey, yes.. i have used datasets.
i post some of my code then maybe you can tell me what wrong =)

Public datap As String = Application.StartupPath & "\database2.mdb"
Private Sub Formcam_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ds = New DataSet
        da = New OleDbDataAdapter
        da.AcceptChangesDuringFill = True
        da.ContinueUpdateonerror = True
        
        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & datap & ";Jet OLEDB:Database Password=*some password*;"
        con.Open()
        sql = "SELECT*FROM memeber"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "Database2")
        con.Close()
        maxrows = ds.Tables("Database2").Rows.Count
        inc = -1
        inc = 0
        ToolStripDropDownButton2.Visible = False
        ToolStripLabel1.Text = "No search yet"
        NavigateRecords()
        Me.BackgroundImage = Image.FromFile(Application.StartupPath & "\Bin\system\bgpic\njdet21.lspc")
        Me.BackgroundImageLayout = ImageLayout.Stretch
    End Sub

Navigate:

Private Sub NavigateRecords()

        txtfm.Text = ds.Tables("Database2").Rows(inc).Item(0)
        txtem.Text = ds.Tables("Database2").Rows(inc).Item(1)
        txtga.Text = ds.Tables("Database2").Rows(inc).Item(2)
        txtpn.Text = ds.Tables("Database2").Rows(inc).Item(3)
        txts.Text = ds.Tables("Database2").Rows(inc).Item(4)
        txttm.Text = "0" & ds.Tables("Database2").Rows(inc).Item(5)
        txtth.Text = "0" & ds.Tables("Database2").Rows(inc).Item(6)
        txtep.Text = ds.Tables("Database2").Rows(inc).Item(26)
        txtp.Text = ds.Tables("Database2").Rows(inc).Item(25)
        DateTimePicker1.Value = ds.Tables("Database2").Rows(inc).Item(7)
        txta1.Text = ds.Tables("Database2").Rows(inc).Item(8)
        txtpc.Text = ds.Tables("Database2").Rows(inc).Item(9)
        txtgf.Text = ds.Tables("Database2").Rows(inc).Item(10)
        cmba.Text = ds.Tables("Database2").Rows(inc).Item(11)
        txthd.Text = ds.Tables("Database2").Rows(inc).Item(12)
        cmbop.Text = ds.Tables("Database2").Rows(inc).Item(14)
        cmbdl.Text = ds.Tables("Database2").Rows(inc).Item(15)
        cmbmt.Text = ds.Tables("Database2").Rows(inc).Item(13)
        txtn.Text = ds.Tables("Database2").Rows(inc).Item(16)
        txttmf.Text = "0" & ds.Tables("Database2").Rows(inc).Item(17)
        txtthf.Text = "0" & ds.Tables("Database2").Rows(inc).Item(18)
        txtgaf.Text = ds.Tables("Database2").Rows(inc).Item(19)
        txtpnf.Text = ds.Tables("Database2").Rows(inc).Item(20)
        txtsf.Text = ds.Tables("Database2").Rows(inc).Item(21)
        txtsb.Text = ds.Tables("Database2").Rows(inc).Item(23)
        txta2.Text = ds.Tables("Database2").Rows(inc).Item(22)
        txtmr.Text = ds.Tables("Database2").Rows(inc).Item(24)
        txtal.Text = ds.Tables("Database2").Rows(inc).Item(28)
        BindingNavigatorPositionItem.Text = ds.Tables("Database2").Rows(inc).Item(24)
    End Sub

save change:

            pg1.Value = 0
            da.SelectCommand = New OleDbCommand(sql, con)
            Dim cb As New OleDb.OleDbCommandBuilder(da)
            pg1.Value = 2
            ds.Tables("Database2").Rows(inc).Item(0) = txtfm.Text
            ds.Tables("Database2").Rows(inc).Item(1) = txtem.Text
            ds.Tables("Database2").Rows(inc).Item(2) = txtga.Text
            ds.Tables("Database2").Rows(inc).Item(3) = txtpn.Text
            ds.Tables("Database2").Rows(inc).Item(4) = txts.Text
            pg1.Value = 10
            ds.Tables("Database2").Rows(inc).Item(5) = txttm.Text
            ds.Tables("Database2").Rows(inc).Item(6) = txtth.Text
            ds.Tables("Database2").Rows(inc).Item(7) = DateTimePicker1.Value.Date
            ds.Tables("Database2").Rows(inc).Item(8) = txta1.Text
            ds.Tables("Database2").Rows(inc).Item(9) = txtpc.Text
            ds.Tables("Database2").Rows(inc).Item(10) = txtgf.Text
            ds.Tables("Database2").Rows(inc).Item(11) = cmba.Text
            ds.Tables("Database2").Rows(inc).Item(12) = txthd.Text
            ds.Tables("Database2").Rows(inc).Item(13) = cmbmt.Text
            ds.Tables("Database2").Rows(inc).Item(14) = cmbop.Text
            ds.Tables("Database2").Rows(inc).Item(15) = cmbdl.Text
            ds.Tables("Database2").Rows(inc).Item(16) = txtn.Text
            pg1.Value = 25
            ds.Tables("Database2").Rows(inc).Item(17) = txttmf.Text
            ds.Tables("Database2").Rows(inc).Item(18) = txtthf.Text
            ds.Tables("Database2").Rows(inc).Item(19) = txtgaf.Text
            ds.Tables("Database2").Rows(inc).Item(20) = txtpnf.Text
            ds.Tables("Database2").Rows(inc).Item(21) = txtsf.Text
            ds.Tables("Database2").Rows(inc).Item(22) = txta2.Text
            ds.Tables("Database2").Rows(inc).Item(23) = txtsb.Text
            ds.Tables("Database2").Rows(inc).Item(24) = txtmr.Text
            ds.Tables("Database2").Rows(inc).Item(25) = txtp.Text
            ds.Tables("Database2").Rows(inc).Item(26) = txtep.Text
            pg1.Value = 60
            da.Update(ds, "Database2")
            pg1.Value = 100
            MsgBox("Record now updated")

Save new

            If inc <> -1 Then
               Dim dsNewRow As DataRow
                pg1.Value = 0
                da.SelectCommand = New OleDbCommand(sql, con)
                Dim cb As New OleDb.OleDbCommandBuilder(da)
                pg1.Value = 2
                dsNewRow = ds.Tables("Database2").NewRow()
                dsNewRow.Item(0) = txtfm.Text
                dsNewRow.Item(1) = txtem.Text
                dsNewRow.Item(2) = txtga.Text
                dsNewRow.Item(3) = txtpn.Text
                dsNewRow.Item(4) = txts.Text
                pg1.Value = 10
                dsNewRow.Item(5) = txttm.Text
                dsNewRow.Item(6) = txtth.Text
                dsNewRow.Item(7) = DateTimePicker1.Value.Date
                dsNewRow.Item(8) = txta1.Text
                dsNewRow.Item(9) = txtpc.Text
                dsNewRow.Item(10) = txtgf.Text
                dsNewRow.Item(11) = cmba.Text
                dsNewRow.Item(12) = txthd.Text
                dsNewRow.Item(13) = cmbmt.Text
                dsNewRow.Item(14) = cmbop.Text
                dsNewRow.Item(15) = cmbdl.Text
                dsNewRow.Item(16) = txtn.Text
                dsNewRow.Item(17) = txttmf.Text
                dsNewRow.Item(18) = txtthf.Text
                dsNewRow.Item(19) = txtgaf.Text
                dsNewRow.Item(20) = txtpnf.Text
                pg1.Value = 25
                dsNewRow.Item(21) = txtsf.Text
                dsNewRow.Item(22) = txta2.Text
                dsNewRow.Item(23) = txtsb.Text
                dsNewRow.Item(24) = txtmr.Text
                dsNewRow.Item(25) = txtp.Text
                dsNewRow.Item(26) = txtep.Text      
                ds.Tables("Database2").Rows.Add(dsNewRow)
                pg1.Value = 50
                da.Update(ds, "Database2")
                pg1.Value = 100
                maxrows = ds.Tables("Database2").Rows.Count
                MsgBox("New record added")
                My.Settings.chang = True
                My.Settings.ny = False
                BindingNavigatorMovePreviousItem.Enabled = True
                BindingNavigatorMoveFirstItem.Enabled = True
                BindingNavigatorMoveNextItem.Enabled = True
                BindingNavigatorMoveLastItem.Enabled = True
                'pg1.Value = 100
            End If
        

And the search

Private Sub ToolStripButton6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton6.Click
        pg1.Value = 0
        'Try
        'Dim tpp As ToolStripMenuItem
        Dim ic As Integer = 0
        Dim sqlsearch As String
        sqlsearch = "SELECT * FROM member WHERE memberNr LIKE '%" & ToolStripTextBox1.Text & "%'"
        Dim adapter As New OleDbDataAdapter(sqlsearch, con)
        dt = New DataTable("member")
        pg1.Value = 20
        adapter.Fill(dt)
        If dt.Rows.Count > 0 Then
            maxrows1 = dt.Rows.Count
            inc1 = -1
            inc1 = 0
            NavigateRecords1()
            pg1.Value = 70
            ToolStripLabel1.Text = "Number of search result: " & maxrows1
            If maxrows1 > 1 Then
                ToolStripDropDownButton2.Visible = True
                ToolStripDropDownButton2.DropDownItems.Clear()
                'If DataReader.HasRows Then
                Do Until ic = maxrows1
                    tdd = New ToolStripLabel
                    Dim ja As String = dt.Rows(ic).Item(0) & " " & dt.Rows(ic).Item(1) & " | " & dt.Rows(ic).Item(24)
                    'ToolStripDropDownButton2.DropDownItems.Add(dt.Rows(ic).Item(0) & " " & dt.Rows(ic).Item(1) & " | " & dt.Rows(ic).Item(24))
                    tdd.Text = dt.Rows(ic).Item(0) & " " & dt.Rows(ic).Item(1) & " | " & dt.Rows(ic).Item(24)
                    tdd.Tag = ic
                    tdd.Dock = DockStyle.Fill
                    tdd.Image = My.Resources.favDock
                    tdd.ImageAlign = ContentAlignment.MiddleLeft
                    tdd.ImageScaling = ToolStripItemImageScaling.SizeToFit
                    ToolStripDropDownButton2.DropDownItems.Add(tdd)

                    AddHandler tdd.MouseDown, AddressOf HandleFavMouseDown
                    ic += 1
                Loop
                'End If
                'ToolStripDropDownButton2.DropDownItems.Add(

            ElseIf maxrows1 = 1 Then
                ToolStripDropDownButton2.Visible = False
            End If
            pg1.Value = 100
        Else
            MsgBox("No Search result")
            'Catch ex As Exception
            'MsgBox(ex.Message)
            'End Try
        End If
        pg1.Value = 0
    End Sub

This post has been edited by EndLessMind: 11 March 2010 - 03:48 AM

[motcom]

Naah should not be a problem then, as the dataset should handle it for you...

Maybe you want to download sqlexpress (unless its already installed, or you have mssql) and create the tables on sql and test the app...

But you should be fine. Have you got any knowledge about installing sql and how to create the tables etc...

[EndLessMind]

motcom, on 11 March 2010 - 02:49 AM, said:

Naah should not be a problem then, as the dataset should handle it for you...

Maybe you want to download sqlexpress (unless its already installed, or you have mssql) and create the tables on sql and test the app...

But you should be fine. Have you got any knowledge about installing sql and how to create the tables etc...

okey? I don't understand how it could handle if for me. but i don't think I'm suppose to understand

No, i don't have any knowledge about that

[motcom]

You'll be amazed what datasets can do,
They can be used in N-Tier applications, where the App is normally devided into 3 parts, Display Logic, Business Logic and DataLogic.

The Dataset can be split into Business and DataLogic, the DataLogic can handle the connections updates etc. to different kind of databases and pass the data back to the Business Logic... But it's a total different approach than what you did...

[EndLessMind]

motcom, on 11 March 2010 - 04:18 AM, said:

You'll be amazed what datasets can do,
They can be used in N-Tier applications, where the App is normally devided into 3 parts, Display Logic, Business Logic and DataLogic.

The Dataset can be split into Business and DataLogic, the DataLogic can handle the connections updates etc. to different kind of databases and pass the data back to the Business Logic... But it's a total different approach than what you did...

Okey

but how do i create a sql database?
i search for sqlexpress on google and got a ms page as result.
but there where only something about "Web Platform Installer"

[motcom]

Hi,

Go to this link

SQL Express 2005

and this one for the management studio

Management Studio sql express 2005

[EndLessMind]

motcom, on 11 March 2010 - 04:46 AM, said:

Hi,

Go to this link

SQL Express 2005

and this one for the management studio

Management Studio sql express 2005

do i create the sql database in the management studio?
please explain it to me

[T3hC13h]

EndlessMind,

Heres a couple of tricks you can use to simply your code.

First you can create a reference to a table in a dataset so you don't have to type ds.Tables("tablename") all over the place and then use the With keyword to further reduce your copy/pasting.

    dim dtDB2 as DataTable = ds.Tables("Database2")
    With dtDB2.Rows(inc)
        .Item(0) = txtfm.Text
        .Item(1) = txtem.Text
        .Item(2) = txtga.Text
        .Item(3) = txtpn.Text
        .Item(4) = txts.Text
    End With

This post has been edited by T3hC13h: 11 March 2010 - 06:39 AM

[EndLessMind]

T3hC13h, on 11 March 2010 - 05:39 AM, said:

EndlessMind,

Heres a couple of tricks you can use to simply your code.

First you can create a reference to a table in a dataset so you don't have to type ds.Tables("tablename") all over the place and then use the With keyword to further reduce your copy/pasting.

    dim dtDB2 as DataTable = ds.Tables("Database2")
    With dtDB2.Rows(inc)
        .Item(0) = txtfm.Text
        .Item(1) = txtem.Text
        .Item(2) = txtga.Text
        .Item(3) = txtpn.Text
        .Item(4) = txts.Text
    End With

hehe.. i´m actually going to reduce my code and speed up my application

[motcom]

Jip,

Did you install SQL Express already? If yes, you might want to uninstall and re-install with the following options:

1. When the screen asks you for Your "Name" and "Company", there is a checkbox for "Hide Advanced Configuration optinos", Uncheck it.

2. At the Features section, mark everything as "entire feature will be installed on local hard drive"
EXCEPT "Replication", we don't want that.

3. Next, when asked for Default or Named Instance, select named instance and write down that name, as you will have to connect to that instance. (You can leave it as SQLExpress)

4. If asked for workstation components, check it

5. On the "Service Account" Screen, select the "use the build-in system account". and use the "network service"

6. On the "Authentication Mode" Screen, select "Mixed Mode" because you will be connectiong to a Server on the web at a later stage, you might as well get used to "Mixed Mode", this option will ask you for a user name and password on the Web Server. Now if you select "Mixed Mode", you will be asked for a "sa" password, Enter a valid password and confirm it. "sa" (case sensitve) is the system administrator and this account is always present on a SQL Server.

7. On the "Coalation Settings" screen, leave everything as default.

8. On the "Configuration Options" screen, check the "Add User to the SQL Administrator Role".

9. Finish the install.

Now you should be able to connect to the SQL Database with the "Server Management Studio Express"...

You can log into the Instance that you supplied (SQLExpress) with "sa" and the password you selected.

A list of items should be on the left hand side and you can expand them and find the database folder.
Right click it and select new->database.

A sql database consist of 2 files a database file and a log file. (mdf and ldf respectively). When created, they can not be deleted except if the SQL Server Service is stopped.

Once your database is created, you should see a folder called "Tables" under your database, right click it to add tables...

(I am not to sure if the Server Management Studio Express can import data, you can try right clicking your database and see if you can import, if yes, you can import from access all your tables to the sql server)

Your connection string to your database might look something like this

"Data Source=SQLExpress;Initial Catalog=YourDatabaseName;Persist Security Info=True;User ID=sa;Password=YourPassword;Connect Timeout=250"


Now once you have it all in place, you can start replacing the
"OleDbDataAdapter" with "SQLDataAdapter" and
"OleDbCommand" with "SQLCommand" and
"OleDbCommandBuilder" with "SQLCommandBuilder"

actually every "OleDb" has a "SQL" equivalent...

big note you need to import the following namespace(s)

Imports System.Data 
Imports System.Data.SQLClient

Well, this will get you started for now.....

[EndLessMind]

motcom, on 11 March 2010 - 05:59 AM, said:

Jip,

Did you install SQL Express already? If yes, you might want to uninstall and re-install with the following options:

1. When the screen asks you for Your "Name" and "Company", there is a checkbox for "Hide Advanced Configuration optinos", Uncheck it.

2. At the Features section, mark everything as "entire feature will be installed on local hard drive"
EXCEPT "Replication", we don't want that.

3. Next, when asked for Default or Named Instance, select named instance and write down that name, as you will have to connect to that instance. (You can leave it as SQLExpress)

4. If asked for workstation components, check it

5. On the "Service Account" Screen, select the "use the build-in system account". and use the "network service"

6. On the "Authentication Mode" Screen, select "Mixed Mode" because you will be connectiong to a Server on the web at a later stage, you might as well get used to "Mixed Mode", this option will ask you for a user name and password on the Web Server. Now if you select "Mixed Mode", you will be asked for a "sa" password, Enter a valid password and confirm it. "sa" (case sensitve) is the system administrator and this account is always present on a SQL Server.

7. On the "Coalation Settings" screen, leave everything as default.

8. On the "Configuration Options" screen, check the "Add User to the SQL Administrator Role".

9. Finish the install.

Now you should be able to connect to the SQL Database with the "Server Management Studio Express"...

You can log into the Instance that you supplied (SQLExpress) with "sa" and the password you selected.

A list of items should be on the left hand side and you can expand them and find the database folder.
Right click it and select new->database.

A sql database consist of 2 files a database file and a log file. (mdf and ldf respectively). When created, they can not be deleted except if the SQL Server Service is stopped.

Once your database is created, you should see a folder called "Tables" under your database, right click it to add tables...

(I am not to sure if the Server Management Studio Express can import data, you can try right clicking your database and see if you can import, if yes, you can import from access all your tables to the sql server)

Your connection string to your database might look something like this

"Data Source=SQLExpress;Initial Catalog=YourDatabaseName;Persist Security Info=True;User ID=sa;Password=YourPassword;Connect Timeout=250"


Now once you have it all in place, you can start replacing the
"OleDbDataAdapter" with "SQLDataAdapter" and
"OleDbCommand" with "SQLCommand" and
"OleDbCommandBuilder" with "SQLCommandBuilder"

actually every "OleDb" has a "SQL" equivalent...

big note you need to import the following namespace(s)

Imports System.Data 
Imports System.Data.SQLClient

Well, this will get you started for now.....

cant install it.. Print screen of the error Here

Edit: Fixed that one

This post has been edited by EndLessMind: 11 March 2010 - 09:39 AM