import excel file to sql 2008 database using vb 6

[recklesslc]

Hi, i have an excel file and i want to convert it to .csv so that it will be easy for me to import the data from excel to sql 2008 using vb 6. Can someone help me please.

[Rabbit]

Open the workbook, click save as, choose csv format. But just so you know, SQL Server has no problem importing from Excel.

[recklesslc]

Hi Sir Rabbit. yes maybe it helps but i have to do it using vb 6 coding. :( I'm a firstimer in vb 6, but seems tenure in sql.

[recklesslc]

and i also do some coding to import excel to sql but i still got this error: Ad hoc access to OLE DB provider 'Microsoft.Jet. OLEDB.4.0' has been denied. You must access this provider through a linked server. Eventhough i already DisallowAdhocAc cess in regedit. :( please help me to sort this out. This has been bothering me for weeks already. :(

[recklesslc]

Here is my code wherein i got OLE DB error:

Private Sub Command2_Click()
Dim excelApp As Excel.Application
Set excelApp = CreateObject("Excel.Application")
Set excelWB = excelApp.Workbooks.Add
Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB;Data Source=sqlserver;" & _
"Initial Catalog=database;User ID=user;Password=password"

'Import by using OPENDATASOURCE.
strSQL = "INSERT INTO mytable SELECT * FROM " & _
"OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', " & _
"'Data Source=" + Text1.Text + ";" & _
"Extended Properties=Excel 8.0')...[sheet1$]"
Debug.Print strSQL
cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
Debug.Print "Records affected: " & lngRecsAff

cn.Close
Set cn = Nothing

End Sub

Please have this corrected if something wrong. Thank you

[Rabbit]

The error message you got is fairly clear. It's not the code, just permissions. The message says you need to set up the workbook as a linked server on the SQL Server. Your database administrators can do this for you if you don't know how.