Updating MS Excell File on a Sharepoint site using VB.net

Hello,

I'm writing an app in VB.net to use an excel file as a database. Right now I have the file local on my PC but want to be able to access the file via SharePoint. When I navigate via web to the excel file (example https://Sharepoint.com/sites/MYsite/documents/Database.xlsx) I need to enter my user name and password.  The code I have is:

    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        Dim Conn As New OleDbConnection
        Dim Comm As New OleDbCommand
        Conn.ConnectionString = CreateConnString("c:\temp\Book1.xls")
        Comm.Connection = Conn
        Comm.CommandText = "Insert INTO [MySheet$] (F1, F2) Values ('1','A')"
        Try
            Conn.Open()
            Comm.ExecuteNonQuery()
            MsgBox("Inserted.")
            Conn.Close()
        Catch ex As Exception
            MsgBox("Error.")
            Conn.Close()
        End Try
    End Sub

Open in new window


Can someone tell me how VB.net can access this document on my SharePoint site using a user name and password?
LVL 1
AnthonySmithMCPAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

unknown_routineCommented:
D you have access to share point Excel file from windows explorer?

If so you should see it in windows explorer:

Something like this:


\\Sharedrive\file\abc.xls


So in your case:

https://Sharepoint.com/sites/MYsite/documents/Database.xlsx)

Becomes: \\Sharepoint.com\sites\MYsite\documents\Database.xlsx


Conn.ConnectionString = CreateConnString(@"\\Sharepoint.com\sites\MYsite\documents\Database.xlsx")
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AnthonySmithMCPAuthor Commented:
No I'm not able to access the file using windows exporer.. I'm running this on Windows 7.. Should that matter?
0
unknown_routineCommented:
OK so first step:

Contact with admin of sharepoint:

You should have access to

\\Sharepoint.com\sites\MYsite\documents\

On Windows explorer on any windows XP, vista, 7, etc
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

AnthonySmithMCPAuthor Commented:
Ok i got it to work with "\\Sharepoint.com\sites\MYsite\documents\" But I'm getting an error in my app because i need to use a user name and password. Where do i put this in my code??

I have a function at the top:

    Private Function CreateConnString(ByVal Str As String) As String

        Return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Str + ";Extended Properties=""Excel 8.0;HDR=YES;"""

    End Function

Open in new window



Do i put it here? How?
0
CodeCruiserCommented:
I think the proper approach would be to check out the file from Sharepoint so that others don't modify it while you are working with it, make changes to it and then check it in. You will have to use Sharepoint Client Object Model for this. Are you willing to go that route?
0
AnthonySmithMCPAuthor Commented:
The application I'm creating will be used by multi users at one time. So are you telling me that only one instance of the application can be ran at one time?
0
CodeCruiserCommented:
Files in sharepoint have to be checked out in order to be modified and only one user can check out a file at a time.
0
Rainer JeschorCommented:
Hi,

username / password: as you have to enter the user/pass when using Internet Explorer, you should check, if your SharePoint root url is configured to be in the "Local Intranet Zone". Normally (and if you run on a domain joined machine) you will no longer be asked for a password.

Check-out (@CodeCruiser): this is NOT true. That depends on the SharePoint list configuration. As long as the list owner did not activate "Force check-out", everyone can easily upload and overwrite existing versions of the file

Which SharePoint version and edition is in use?
Why do you want to use Excel as a database - especially in the case of multiple user making adjustments?
What is your business case?
IMHO Excel is the worst place to be used as a database. Perhaps you might think of using a SharePoint list or a real database for handling your business requirements.

HTH
Rainer
0
CodeCruiserCommented:
If the user is not required to checkout, then file would be overwritten by multiple users using it at the same time.
0
AnthonySmithMCPAuthor Commented:
@Rainer the application I’m creating is for time management. the reason I’m using SharePoint is because the database have to be online and accessible by everyone who is running the application. The version of SharePoint is Windows SharePoint services 2.0.

I was able to get my code to work by using the url \\Sharepoint.com\sites\MYsite\documents\. so that’s a good thing. My only concern now is will multiple people be able to edit/view this database though by vb.net application. Is there another databse you recommend? I know VB.net has a lot of functions with MS Access. But again.. the database needs to be stored online somewhere.
0
CodeCruiserCommented:
Do you have SQL server installed on any of your servers?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.