SQL n00b needs help!

[Stift]

Hi everyone, I just get a Excel file with a commandbutton which will
import some data from an Oracle/SQL db.
But the SQL code is not what I want.
I haven't got any experience with connecting to a db with VBA.

This is what I have to do:
SELECT * from SACS_LOTS
where LOT_NUMBER like '27%';

I really appreciate all the help I get.!!!

10000000x thanks in advance for helping me!

This Is the code I get and work but doesn't get the right value for
me.


Code:
--------------------


Private Sub cmdSACS_Click()
Dim mijnConnection As ADODB.Connection 'maak een object aan
Dim mijnRecordSet As ADODB.Recordset 'maak een object aan
Dim mijnSQL As String
Dim maxRecords As Integer
Static CopymaxRecords As Integer
Dim teller As Integer


If CopymaxRecords > 0 Then
For teller = 1 To CopymaxRecords
Worksheets("sheet1").Cells(teller, 2) = ""
Next

End If

Set mijnConnection = New ADODB.Connection
Set mijnRecordSet = New ADODB.Recordset
mijnConnection.ConnectionString = "Provider=blabla.1;Password=blabla;User ID=blabla;Data Source=blabla;Persist Security Info=True"
mijnConnection.Open

mijnSQL = mijnSQL & " Select count(*) "
mijnSQL = mijnSQL & " from SACS_PALLETS "

mijnRecordSet.ActiveConnection = mijnConnection
mijnRecordSet.Source = mijnSQL
mijnRecordSet.Open
maxRecords = mijnRecordSet.Fields.Item(0).Value
CopymaxRecords = maxRecords

mijnRecordSet.Close

'****************************************************
mijnSQL = ""
mijnSQL = mijnSQL & " Select * "
mijnSQL = mijnSQL & " from SACS_PALLETS"

mijnRecordSet.Source = mijnSQL
mijnRecordSet.Open

For teller = 1 To maxRecords
'List1.AddItem mijnRecordSet.Fields.Item("CustomerID").Value
Worksheets("sheet1").Cells(teller, 2) = mijnRecordSet.Fields.Item("LOT_LOT_NUMBER").Value
mijnRecordSet.MoveNext
Next

mijnRecordSet.Close
Set mijnRecordSet = Nothing

mijnConnection.Close
Set mijnConnection = Nothing
End Sub

 

[briansol]

I'm not sure what you're after but here is routine I've made whic
dynamicly lists the content of a table from SQL server(might be of som
help):


Code
-------------------

Public Sub ImportTable()
Dim i, j As Integer ' Counters

Set con = New ADODB.Connection
Set rst = New ADODB.Recordset

ActiveSheet.Range("A7:GZ10000").Value = ""

con.ConnectionString = "Provider=SQLOLEDB.1;Password=;Persist Security Info=True;User ID=;Initial Catalog=;Data Source="
con.Open
rst.Open "SELECT TOP 100 * FROM USER", con, 1, 1
'--------------------------------------
' Dynamic listing
'-------------------------------------
' i = row, j = column
i = 8
If Not rst.EOF Then
'------------------------------
' Fields Names
'------------------------------
For j = 1 To rst.Fields.Count
ActiveSheet.Cells(i - 1, j) = rst.Fields(j - 1).Name
Next
'------------------------------
' Field Rows
'------------------------------
Do While Not rst.EOF
For j = 1 To rst.Fields.Count
ActiveSheet.Cells(i, j) = rst.Fields(j - 1).Value
Next
i = i + 1
rst.MoveNext
Loop
End If
rst.Close
con.Close
End Sub

 

[Stift]

If I change the SQL with my own SQl-statements I get a error,
I think it has something to do with the like '27%' ???

 

[briansol]

LIKE '27%' should work with SQL Server.

What kind of error do you get?

Also try just "SELECT * from SACS_LOTS" to see if that's working

 

[Stift]

Don't needed anymore,

With MS Query it works perfect