[RESOLVED] OldDB Insert cmd- source DB password required

[MechEng]

In trying to update an ACCESS2003 database from VB2005 using OleDB, I have run into a problem of needing to provide a password to access the source database when using the INSERT command. Here is the code I am using to move the data.

        If UpdateRequired = True Then
            'Old Database exists... Repopulate the new database


            Dim strSQL As String
            Dim conString As String = My.Settings.ScoreboardConnectionString
            Dim conn As OleDb.OleDbConnection = New OleDb.OleDbConnection(conString)
            Dim command As OleDb.OleDbCommand

            strSQL = "INSERT INTO tbl_GameData "
            strSQL = strSQL & "Select * From  tbl_GameData in " & "'" & StrPath & "\" & SourceDBFileName & "'"

            Console.WriteLine(strSQL)

            command = New OleDb.OleDbCommand(strSQL, conn)

            conn.Open()
            command.ExecuteNonQuery()


            conn.Close()
            conn.Dispose()

        End If

Is there a way to provide the password the the source database or will need to open a second connection and use a different INSERT strategy?

[pvbangera]

what have u defined in ScoreboardConnectionString? Passwords are generally mentioned in connection string.

[MechEng]

The ScoreboardConnectionString works for the local database. The entire routine works IF the password is removed from the remote database.

What is needed is a way to pass the password details for the Source (remote) database with the SQL statement.

[pvbangera]

Can you provide the ConnectionString that you are using?

[si_the_geek]

The connection string is completely irrelevant in this case - the problem is not connecting to the database, but getting the database itself to connect to another one.

Based on previous tests, rather than using IN for that, to be able to use a password you need to use different syntax, eg:

strSQL = strSQL & "Select * From [MS Access;DATABASE=" & StrPath & "\" & SourceDBFileName & ";Uid=admin;Pwd=password;].[tbl_GameData]"

[MechEng]

PERFECT! That worked perfectly perfect in fact.

Si, thank you very much.

I am going to mark this thread as Resolved, but I would really like to know where you dug up that little tidbit… I am guessing an obscure SQL reference manual.

[si_the_geek]

Unfortunately I'm not sure where I found it originally, presumably on the forums somewhere - or perhaps on another unofficial site.

I keep a bookmark to the thread where I first discovered it/worked it out: http://www.vbforums.com/showthread.p...99#post2585899
.. but I didn't mention where I found it (just that I haven't found any official documentation).


I eventually managed to find official documentation for the IN syntax, which is why I have added it to the Database Development FAQs/Tutorials (at the top of this forum), but unfortunately I still haven't found anything for this syntax.