Error (Trying to Load Excel data To SQL)

[MNpoker]

I got this from Microsoft (I am using Excel 7.0).

' Load The description Tables into SQL
    'Import by using OPENDATASOURCE.
    VtSQL = "SELECT * INTO TempDesc_Bldg FROM " & _
        "OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', " & _
        "'Data Source=" & Application.ActiveWorkbook.Path & "\" & Application.ActiveWorkbook.Name & ";" & _
        "Extended Properties=Excel 8.0')...[Desc_Bldg$]"
    ObjCommand.CommandText = VtSQL
    ObjCommand.Execute VtSQL

I am already running SQL queries into Excel and that is working fine.

I am getting the follow error:
Run-time error '-2147217900 (80040e14)':

OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize:: Initialize rturned 0x80004005: The provider did not give information about the error.].

Any help is greatly appreciated!!

 

[ChrisM]

Not that this is very helpful, but when you start doing queries in code through excel, you start to run into ALL KINDS of goofy behavior. Like maybe you have double quotes in your query," ", and it works fine from the database program itself, but when you put the quotes in an excel sql statement it bombs out on you.

Your best bet is to start with a very simple query and troubleshoot from there.

Also, there are 3 or 4 different ways to do queries in code. You can try making an ADO recordset object and see if that works for example.

Also may help to specify the source database you are using. You reference Jet so I'm assuming it's probably Access?

Good luck!

 

[MNpoker]

Not that this is very helpful, but when you start doing queries in code through excel, you start to run into ALL KINDS of goofy behavior. Like maybe you have double quotes in your query," ", and it works fine from the database program itself, but when you put the quotes in an excel sql statement it bombs out on you.

Your best bet is to start with a very simple query and troubleshoot from there.

Also, there are 3 or 4 different ways to do queries in code. You can try making an ADO recordset object and see if that works for example.

Also may help to specify the source database you are using. You reference Jet so I'm assuming it's probably Access?

Good luck!

Thanks it's SQL and here is more of the Code:

Sub RunQueries()

' Runs The Report
' ----------------------------------------------------------------------------------------------
 

'Connect to Server
sServer = "OLEDB;Provider=SQLOLEDB.1;password=xxxx;Persist Security Info=True;User ID=sa;Data Source=xxxx"
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCommand = 1

Set ObjConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
Set ObjCommand = CreateObject("ADODB.Command")

'Load EDM and PORTID Name
sEDM = Worksheets("Inputs").Range("B1")
sPortID = Worksheets("Inputs").Range("B2")

' Open Connection
ObjConnection.Open = "Provider=SQLOLEDB;Data Source=" & sServer & ";Trusted_Connection=Yes;Initial Catalog=" & sEDM
    
ObjCommand.CommandType = adCommand
ObjCommand.ActiveConnection = ObjConnection
    
This is what I'm having problems with - below

' Load The description Tables into SQL
    'Import by using OPENDATASOURCE.
    VtSQL = "SELECT * INTO TempDesc_Bldg FROM " & _
        "OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', " & _
        "'Data Source=" & Application.ActiveWorkbook.Path & "\" & Application.ActiveWorkbook.Name & ";" & _
       "Extended Properties=Excel 8.0')...[Desc_Bldg$]"

    ObjCommand.CommandText = VtSQL
    ObjCommand.Execute VtSQL
    
    
'CREATE AND RUN QUERIES

' ValidTIV

(THIS PART - BELOW) - WORKS FINE)

VtSQL = "SELECT Sum(loccvg.VALUEAMT) AS TIVVALUE " & _
"FROM portacct INNER JOIN (loccvg INNER JOIN loc ON loccvg.LOCID = loc.LOCID) ON portacct.ACCGRPID = loc.ACCGRPID " & _
"GROUP BY loccvg.PERIL, loccvg.ISVALID, portacct.PORTINFOID" & _
" HAVING (((loccvg.ISVALID)=1) AND ((portacct.PORTINFOID)=" & sPortID & "));"
    
ObjCommand.CommandText = VtSQL
Set rst = ObjCommand.Execute

' Put Info On Worksheet
Worksheets("DataPrimary").Range("c3").CopyFromRecordset rst

 

[MNpoker]

Bump for one more try.

My code is INDENTICAL to what MS shows to do ??

Use ADO and SQLOLEDB
When you are connected to SQL Server in an ADO application by using Microsoft OLE DB for SQL Server (SQLOLEDB), you can use the same "distributed query" syntax from the Using Distributed Queries section to import Excel data into SQL Server.

The following Visual Basic 6.0 code sample requires that you add a project reference to ActiveX Data Objects (ADO). This code sample also demonstrates how to use OPENDATASOURCE and OPENROWSET over an SQLOLEDB connection. Dim cn As ADODB.Connection
Dim strSQL As String
Dim lngRecsAff As Long
Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB;Data Source=<server>;" & _
"Initial Catalog=<database>;User ID=<user>;Password=<password>"

'Import by using OPENDATASOURCE.
strSQL = "SELECT * INTO XLImport6 FROM " & _
"OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', " & _
"'Data Source=C:\test\xltest.xls;" & _
"Extended Properties=Excel 8.0')...[Customers$]"
Debug.Print strSQL
cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
Debug.Print "Records affected: " & lngRecsAff

 

[Blade Hunter]

Does your table have the correct number of columns to take everything from your Excel workbook?

I find this:

    VtSQL = "SELECT * INTO TempDesc_Bldg FROM " & _
        "OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', " & _
        "'Data Source=" & Application.ActiveWorkbook.Path & "\" & Application.ActiveWorkbook.Name & ";" & _
       "Extended Properties=Excel 8.0')...[Desc_Bldg$]"

to be quite an odd way of doing it. I always build a complete SQL statement into a variable first then pass that to my ado connection. This way you can do transaction level logging and you can post to the DB line by line and read back what you wrote, if it is equal, shade the row green, if not, shade it red.

Selecting * into from a workbook is kind of making a huge assumption that your workbook will be an exact copy of the table you are inserting into.

Anyway, I am getting off topic here but can you put a stop just below the line I have posted above and go to the debugged and type:

?VtSQL

I think the issue is in there somewhere.

 

[MNpoker]

I made some changes to the code: Still getting same error though.

 VtSQL = "Create Table TempDesc_Bldg (BLDGSCHEME char(5), BLDGCLASS Char(5), BLDGDesc Char(50))"
    ObjCommand.CommandText = VtSQL
    ObjCommand.Execute VtSQL

The above works
       
    VtSQL = "SELECT * INTO TempDesc_Bldg FROM " & _
        "OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', " & _
        "'Data Source=" & Application.ActiveWorkbook.Path & "\" & Application.ActiveWorkbook.Name & ";" & _
       "Extended Properties=Excel 8.0')...[Desc_Bldg$]"
    ObjCommand.CommandText = VtSQL
    ObjCommand.Execute VtSQL

Though when I do this:
' Delete The Description tables
VtSQL = "Drop Table TempDesc_Bldg"
ObjCommand.Execute VtSQL

It tells me the table already exists? Yeah I know I want to delete it!

VtSQL = SELECT * INTO TempDesc_Bldg FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=Z:\CATModeling\WorkInProgress\BasicExposureReport_v1.xlsm;Extended Properties=Excel 8.0')...[Desc_Bldg$]

Thanks a TON for your help!!

Sorry for the confusion on my part two things going on here now. I am now creating the table and trying to insert values but haven't gotten there yet.

 

[Blade Hunter]

OK, So can you do a desc TempDesc_Bldg on the DB to show you the columns in the table and also grab a count of the columns from the spreadsheet you are trying to upload.

If you post them here we might be able to see some sort of irregularity.