Run-time error '-2147467259 (80004005) ???

G

Gulredy

New Member
Joined
Aug 21, 2012
Messages
24
I have a code, which runs on a button hit.
When you hit the button, it adds a path paramterers to the variable mappa, and then from that it creates a new file.

After that I'm connecting to sql, then I want to export datas based on the values of the cells. The values of the cells is given to a variable, and I want to give the sql those variables values.
I don't know if this is correct but without these lines its gives the same error, so I can't even try them out!

So on the end it gives me an error like this.

Run-time error '-2147467259 (80004005)':
Automation error

What could it mean? And how can I fix it?


Sub INDITAS3(mappa As String)
'excel.txt megnyitása
Workbooks.OpenText Filename:= _
mappa & "\excel.txt", Origin:=1250, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
Array(3, 1)), TrailingMinusNumbers:=True

'-----csatlakozás
Dim cn As ADODB.connection
Dim strConn As String
Dim strSQL As String
Dim strXLSource As String
'kapcsolódás
strConn = strConn & "Provider=SQLOLEDB;Data Source=;"
strConn = strConn & "Initial Catolog=;Trusted_Connection=YES"
Set cn = New ADODB.connection
cn.Open "DRIVER={MySQL ODBC 5.1 Driver}" _
& ";SERVER=localhost" _
& ";DATABASE=test3" _
& ";UID=root" _
& ";PWD=root" _
& ";OPTION=16427"
strXLSource = "c:\david\NE_Arfolyam 2.xlsm;Extended Properties=Excel 12.0"
'---------

'excel.txt feldolgozása
Dim wb As Workbook
Set wb = Workbooks("excel.txt")
Dim ws As Worksheet
Set ws = wb.Sheets(1)

'ThisWorkbook.Activate 'az NE_Arfolyam excel fájl aktiválása

Dim datumido As String
datumido = ws.Range("A1").Value 'az A1 cella értékét átadjuk a datumido változónak

Dim nev, valutaneve As String
Dim i As Integer
Dim v_eladas, v_vetel As Integer
i = 2
Do While ws.Range("A" & i).Value <> "" '???addig amíg A i-edik értéke #
If ws.Range("A" & i).Value <> "***" Then 'hogyha a létrehozott excel file A oszlop +i-edik értékén *** van akkor befejezi
'1es if
If ws.Range("A" & i).Value = "#" Then 'hogyha a létrehozott excel file A oszlop +i-edik értékén # akkor megy tovább a kódban mert a fájlban # után valuták vannak
nev = ws.Range("B" & i).Value 'a megnyitott excel file B2-es mezőjében szereplő értéket a nev változónak adjuk
strSQL = "INSERT INTO arfolyam (irodanev)"
strSQL = strSQL & "VALUES ("" & nev & "")"
'1es else
Else
'konkurencia árfolyam megfelelő helyre írása
valutanev = ws.Range("A" & i).Value 'itt megkapjuk a valuta nevét
strSQL = "INSERT INTO arfolyam (valutanev)"
strSQL = strSQL & "VALUES ("" & valutaneve & "")"

v_eladas = ws.Range("B" & i).Value
strSQL = "INSERT INTO arfolyam (eladas)"
strSQL = strSQL & "VALUES ("" & v_eladas & "")"

v_vetel = ws.Range("C" & i).Value
strSQL = "INSERT INTO arfolyam (vetel)"
strSQL = strSQL & "VALUES ("" & v_vetel & "")"
End If '1es else
End If '1es if
i = i + 1

Loop

Debug.Print strSQL
cn.Execute strSQL, adExecuteNoRecords <-- IT GIVES THE ERROR HERE


End Sub
 
Last edited:

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sort by date Sort by votes
Can you post debug.print StrSql value dispalyed in Immediate window ?
 
Last edited:
Upvote 0
Can you post debug.print StrSql value dispalyed in Immediate window ?
Click to expand...
It writes this:
INSERT INTO arfolyam (vetel)VALUES (" & v_vetel & ")

Even if I comment out all the lines from Do While to Loop
It writes an error (but this time with another number)

Run-time error '-2147217908 (80040e0c)
Automation error
 
Last edited:
Upvote 0
Gulredy said:
It writes this:
INSERT INTO arfolyam (vetel)VALUES (" & v_vetel & ")

Even if I comment out all the lines from Do While to Loop
It writes an error (but this time with another number)

Run-time error '-2147217908 (80040e0c)
Automation error
Click to expand...


There are lot of bugs in your code..

You May want to check the connection to MYSQL database

SQL Statment are not properly syntaxed..

Generally its ,
INSERT INTO table_name
VALUES (value1, value2, value3,...)
 
Upvote 0
Thing is that this connection method is working in another module, I've just copy/paste it, but it won't work!
As I said I've commented out every line from the Do while to the Loop, so they dont interfere, but there is still an error!
How is it possible that, this connection code is worked (and is working) on another module, but not here?
 
Upvote 0
Check the Reference to ADO in your new workbook.. To do this Goto VBA editor > Tools > Reference > Microsoft Activex Data Objects 2.0 Library has to be checked..
 
Upvote 0
Yes that is checked!

Heres a code from another module it works fine without any problem! What could be the problem with the first one??

Sub export()
'----------Adatok exportálása adatbázisba
Dim cn As ADODB.connection
Dim strConn As String
Dim strSQL As String
Dim strXLSource As String
'kapcsolódás
strConn = strConn & "Provider=SQLOLEDB;Data Source=;"
strConn = strConn & "Initial Catolog=;Trusted_Connection=YES"
Set cn = New ADODB.connection
cn.Open "DRIVER={MySQL ODBC 5.1 Driver}" _
& ";SERVER=localhost" _
& ";DATABASE=test3" _
& ";UID=root" _
& ";PWD=root" _
& ";OPTION=16427"
strXLSource = "c:\david\NE_Arfolyam 2.xlsm;Extended Properties=Excel 12.0"
'----
Sheets("Setup").Activate 'setup munkalap aktiválása

For i = 4 To 16
strSQL = "INSERT INTO valuta (nev)" 'itt adjuk meg, hogy mely sql mezőkbe akarunk értéket beszúrni.
strSQL = strSQL & "VALUES (""" & Cells(i, 16).Text & """)" 'melyik sor, melyik oszlop értékét adjuk be mivel a táblában Valuta_Lista mező varchar ezért " " közé kell tenni az értéket!
cn.Execute strSQL, adExecuteNoRecords 'végrehajtás
Next i

cn.Close 'kapcsolat lezárása
'-----------exportálás vége
End Sub
Click to expand...
 
Upvote 0
I've rewrited the code a bit, it should work, but i have the 80040E14 error code! Still don't know why it happens

Heres the code:





Code: 
Option Explicit
Sub INDITAS3(mappa As String)
 'excel.txt megnyitása
    Workbooks.OpenText Filename:= _
        mappa & "\excel.txt", Origin:=1250, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, Comma:=False _
        , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
        Array(3, 1)), TrailingMinusNumbers:=True
        
    '----------Adatok exportálása adatbázisba
    Dim cn As ADODB.connection
    Dim strConn As String
    Dim strSQL As String
    Dim strXLSource As String
   'kapcsolódás
    strConn = strConn & "Provider=SQLOLEDB;Data Source=;"
    strConn = strConn & "Initial Catolog=;Trusted_Connection=YES"
    Set cn = New ADODB.connection
    cn.Open "DRIVER={MySQL ODBC 5.1 Driver}" _
        & ";SERVER=localhost" _
        & ";DATABASE=test3" _
        & ";UID=root" _
        & ";PWD=root" _
        & ";OPTION=16427"
    strXLSource = "c:\david\NE_Arfolyam 2.xlsm;Extended Properties=Excel 12.0"
    '----
    
    'excel.txt feldolgozása
    Dim wb As Workbook
    Set wb = Workbooks("excel.txt")
    Dim ws As Worksheet
    Set ws = wb.Sheets(1)
    
    'ThisWorkbook.Activate      'az NE_Arfolyam excel fájl aktiválása
    
    Dim datumido As String
    datumido = ws.Range("A1").Value     'az A1 cella értékét átadjuk a datumido változónak
    
    Dim nev, valutaneve As String
    Dim i As Integer
    Dim v_eladas, v_vetel As Integer
    
    i = 2
    Do While ws.Range("A" & i).Value <> ""  '???addig amíg A i-edik értéke #
        If ws.Range("A" & i).Value <> "***" Then     'hogyha a létrehozott excel file A oszlop +i-edik értékén *** van akkor befejezi
            '1es if
            If ws.Range("A" & i).Value = "#" Then    'hogyha a létrehozott excel file A oszlop +i-edik értékén # akkor megy tovább a kódban mert a fájlban # után valuták vannak
                nev = ws.Range("B" & i).Value     'a megnyitott excel file B2-es mezőjében szereplő értéket a nev változónak adjuk
                'strSQL = "INSERT INTO arfolyam (irodanev)"
                'strSQL = strSQL & "VALUES ("" & nev & "")"
                    '1es else
                    Else
                    'konkurencia árfolyam megfelelő helyre írása
                    valutaneve = ws.Range("A" & i).Value 'itt megkapjuk a valuta nevét
                    v_eladas = ws.Range("B" & i).Value
                    v_vetel = ws.Range("C" & i).Value
                    strSQL = "INSERT INTO arfolyam (irodanev,valutanev,vetel,eladas,datum)"
                    strSQL = strSQL & "VALUES (" & nev & ",""" & valutaneve & """,""" & v_vetel & """,""" & v_eladas & """,""" & datumido & """)"
                    Debug.Print strSQL
                    cn.Execute strSQL, adExecuteNoRecords
                    End If '1es else
            End If '1es if
            i = i + 1


    Loop
    


    cn.Close
    
 
End Sub



Plus information that I have a database with the following tabel / fields:
database_www.kepfeltoltes.hu_.jpg

It might be that I'm doing something wrong with the database?
 
Upvote 0
I've got the solution!

It was a syntax error which is generated by the " signs in the part where I want to insert.
The correct line is below, for those who have similar issue it might help:

Code: 
strSQL = strSQL & "VALUES (""" & nev & """,""" & valutaneve & """,""" & v_vetel & """,""" & v_eladas & """,'" & datumido & "')"
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
Sql insert problem with double type numbers!
Replies
6
Views
4K
Gulredy
G

Forum statistics

Threads
1,215,036
Messages
6,122,794
Members
449,095
Latest member
m_smith_solihull

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top