none
Excel VBA to MSSQL DB - sending TWICE or more [Solved] RRS feed

  • General discussion

  • Hello everybody

    I just want to write about my experience with VBA and sending to MSSQL database. Long time I got a problem that query was execute TWICE or more. Based on opinions and posts from this forum and others I think I found solution.

    Query sending twice during  two commands

    '(1)

    Set rcrds = conn.Execute(SQLstr)' this is enough for writing new SINGLE record to DB

    '(2)

    conn.Execute (SQLstr) ' this is making records TWICE and should be hidden

    Below is my VBA code which works now wirk my excel and my client DB. I hope it will be helpfull

    Regards

    Sub VBA_MSSQL() 'DB Variables Dim login As String Dim passwd As String Dim host As String Dim dbname As String 'Connection Variables Dim conn As ADODB.Connection 'SQL request Variable Dim SQLstr As String 'System variables Dim DomainName, ComputerName, UserName 'Get data connection (put Your data hete) login = "TypeYourLogin" passwd = "TypeYourPass" host = "TypeYourHostName" 'it should be txtName or IP - depends on various reasons dbname = "TypeYourDbName" 'Get info about user DomainName = Environ("UserDomain") ComputerName = Environ("ComputerName") UserName = Environ("UserName") 'Connection config and initialize Set conn = New ADODB.Connection With conn .ConnectionString = "Provider=SQLOLEDB.1;Password=" & passwd & ";Persist Security Info=True;User ID=" & login & ";Initial Catalog=" & dbname & ";Data Source=" & host & ";Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=" & ComputerName & ";Use Encryption for Data=False;Tag with column collation when possible=False" .Open 'Check connection If conn.State = 0 Then DoEvents ' ConnectToDB = False MsgBox ("Connection fail") End Else DoEvents ' ConnectToDB = True End If End With 'Exemplary MSSQL Request for writing new record (query typed as string) SQLstr = "INSERT INTO [YourDbName].[dbo].[YourTableNAme] ([No],[Type],[CREATED],[INSERTED_BY] ) VALUES ('1','Default',"'2019-04-08'","'unknown@unknown.pl'")"



    '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

    'problem solving

    'Query execution and saving Set rcrds = conn.Execute(SQLstr)' this is enough for writing new SINGLE record to DB ' conn.Execute (SQLstr) ' this is making records TWICE and should be hidden

    '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!


    'Connection close conn.Close Set rcrds = Nothing End Sub



    Monday, April 8, 2019 9:17 PM

All replies

  • hey! this should probably be moved to the Excel VBA forum. Hopefully a moderator will take care of moving the threat to the correct forum for you.
    Tuesday, April 9, 2019 1:39 AM