VB 6.0 and SQL Connection Problem

abhijnan · August 7th, 2010, 01:34 AM

Hello,

I am a beginner in coding with VB 6.0. While i tried to learn how to fetch data from Database into a UI form by SQL Queries, i tried the following code with a text file (myTable.txt) being used as data source which is kept under the path C:\DB.

Private Sub Command1_Click()
Dim connectionText As New ADODB.Connection
Dim recordSetObj As New ADODB.Recordset
Dim path As String

path = "C:\DB\"

connectionText.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" _
& path & ";Extended Properties='text;HDR=YES;FMT=Delimited'"

recordSetObj.Open "Select * From myTable.txt WHERE Year=1977;", _
connectionText, adOpenStatic, adLockReadOnly, adCmdText
Do While Not recordSetObj.EOF
MsgBox recordSetObj(0) & ", " & recordSetObj(1) & ", " & recordSetObj(2)
recordSetObj.MoveNext
Loop

End Sub

I got the above code from the link given below
http://www.ehow.com/way_5568882_visu...-tutorial.html

This code runs perfectly OK when am using text file as database and it fetches the required data into message box too. But when I am using excel as datasource it gives me an error saying the database is read only. I modify the code as below to look up the excel file though.

Private Sub Command1_Click()
Dim connectionText As New ADODB.Connection
Dim recordSetObj As New ADODB.Recordset
Dim path As String

path = "C:\DB\"

connectionText.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" _
& path & ";Extended Properties='text;HDR=YES;FMT=Delimited'"

recordSetObj.Open "Select * From myTable.xlsx WHERE Year=1977;", _
connectionText, adOpenStatic, adLockReadOnly, adCmdText
Do While Not recordSetObj.EOF
MsgBox recordSetObj(0) & ", " & recordSetObj(1) & ", " & recordSetObj(2)
recordSetObj.MoveNext
Loop

End Sub

Can i get a help about why this is happening and how to resolve this?

Old Pedant · August 8th, 2010, 03:48 PM

That's not how you connect to and read from Excel. You do *NOT* treat Excel as a text file (because it isn't).

Look here:
http://www.carlprothman.net/Default....orMicrosoftJet

Scroll down to the section titled "You can also open an Excel Spreadsheet using the JET OLE DB Provider"

So you will want something like

Code:

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=" &* path & "\myTable.xlsx;" & _
           "Extended Properties=""Excel 10.0;HDR=Yes""" 
recordSetObj.Open "Select * From [sheetname$] WHERE [Year]=1977;", oConn

Do *NOT* use adOpenStatic unless you really need to. And I'm not sure it will work with Excel, anywyay.

I am *guessing* that you need "Excel 10.0" as the Extended Properties value. Have not worked with ".xslx" (only with ".xls") so I'm not sure. Play with it.

******

Note that you *MUST* put [...] around the sheetname and must append the $ to the end of the name.

Also, note that YEAR is a keyword in JET SQL so you must put the [...] around it, as well.