I cannot make this query work...

first I make the sql string like this:

sSQLString = "SELECT * FROM [tblBASE] WHERE VESSEL LIKE 'MSC*' "

Then I use this string in the following code, connecting to an Access
database. I just want to retreive data of all records where vessel starts
with "MSC".

Public Sub retrieveData(sSQL As String)

Dim RECSET As ADODB.Recordset
Dim connectionString As String
Dim fila As Long
Dim sheet As Worksheet

Set sheet = ActiveSheet


connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ActiveWorkbook.path & "\Tracking.mdb;"

fila = 5

Set RECSET = New ADODB.Recordset
Call RECSET.Open(sSQL, _
connectionString, , , _
CommandTypeEnum.adCmdText)

If Not RECSET.EOF Then

Call sheet.Range("a" & fila).CopyFromRecordset(RECSET)

Else

Call MsgBox("Error: No data found", vbCritical)

End If

With sheet.Range("5:5000")
.RowHeight = 15
End With

If (RECSET.State And ObjectStateEnum.adStateOpen) Then RECSET.Close
Set RECSET = Nothing

End Sub

Any idea that may help me???

thanks! bregards Santiago

Just to add some (maybe) useful info: if the sSQLstring is:

sSQLString = "SELECT * FROM [tblBASE]"

the query brings every record in the table, but cannot filter MSC* in the
sql query...

thanks.

bregards
Santiago

"Santiago" wrote:

I cannot make this query work...

first I make the sql string like this:

sSQLString = "SELECT * FROM [tblBASE] WHERE VESSEL LIKE 'MSC*' "

Then I use this string in the following code, connecting to an Access
database. I just want to retreive data of all records where vessel starts
with "MSC".

Public Sub retrieveData(sSQL As String)

Dim RECSET As ADODB.Recordset
Dim connectionString As String
Dim fila As Long
Dim sheet As Worksheet

Set sheet = ActiveSheet


connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ActiveWorkbook.path & "\Tracking.mdb;"

fila = 5

Set RECSET = New ADODB.Recordset
Call RECSET.Open(sSQL, _
connectionString, , , _
CommandTypeEnum.adCmdText)

If Not RECSET.EOF Then

Call sheet.Range("a" & fila).CopyFromRecordset(RECSET)

Else

Call MsgBox("Error: No data found", vbCritical)

End If

With sheet.Range("5:5000")
.RowHeight = 15
End With

If (RECSET.State And ObjectStateEnum.adStateOpen) Then RECSET.Close
Set RECSET = Nothing

End Sub

Any idea that may help me???

thanks! bregards Santiago

Try

"SELECT * FROM [tblBASE] WHERE VESSEL LIKE 'MSC%' "

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"Santiago" wrote:
| Just to add some (maybe) useful info: if the sSQLstring is:
|
| sSQLString = "SELECT * FROM [tblBASE]"
|
| the query brings every record in the table, but cannot filter MSC* in the
| sql query...
|
| thanks.
|
| bregards
| Santiago
|
| "Santiago" wrote:
|
| I cannot make this query work...
|
| first I make the sql string like this:
|
| sSQLString = "SELECT * FROM [tblBASE] WHERE VESSEL LIKE 'MSC*' "
|
| Then I use this string in the following code, connecting to an Access
| database. I just want to retreive data of all records where vessel
starts
| with "MSC".
|
| Public Sub retrieveData(sSQL As String)
|
| Dim RECSET As ADODB.Recordset
| Dim connectionString As String
| Dim fila As Long
| Dim sheet As Worksheet
|
| Set sheet = ActiveSheet
|
|
| connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
| "Data Source=" & ActiveWorkbook.path & "\Tracking.mdb;"
|
| fila = 5
|
| Set RECSET = New ADODB.Recordset
| Call RECSET.Open(sSQL, _
| connectionString, , , _
| CommandTypeEnum.adCmdText)
|
| If Not RECSET.EOF Then
|
| Call sheet.Range("a" & fila).CopyFromRecordset(RECSET)
|
| Else
|
| Call MsgBox("Error: No data found", vbCritical)
|
| End If
|
| With sheet.Range("5:5000")
| .RowHeight = 15
| End With
|
| If (RECSET.State And ObjectStateEnum.adStateOpen) Then RECSET.Close
| Set RECSET = Nothing
|
| End Sub
|
| Any idea that may help me???
|
| thanks! bregards Santiago

What do you mean by "cannot make this query work"? Cab you be more specific?

Does this SQL string work in Access?

Do you get an error message in Excel or just no records returned? Since
your simpler query works I would suspect it is a problem on the Access
end rather than with VBA.

HTH
G

Santiago wrote:
Just to add some (maybe) useful info: if the sSQLstring is:

sSQLString = "SELECT * FROM [tblBASE]"

the query brings every record in the table, but cannot filter MSC* in the
sql query...

thanks.

bregards
Santiago

"Santiago" wrote:


I cannot make this query work...

first I make the sql string like this:

sSQLString = "SELECT * FROM [tblBASE] WHERE VESSEL LIKE 'MSC*' "

Then I use this string in the following code, connecting to an Access
database. I just want to retreive data of all records where vessel starts
with "MSC".

Public Sub retrieveData(sSQL As String)

Dim RECSET As ADODB.Recordset
Dim connectionString As String
Dim fila As Long
Dim sheet As Worksheet

Set sheet = ActiveSheet


connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ActiveWorkbook.path & "\Tracking.mdb;"

fila = 5

Set RECSET = New ADODB.Recordset
Call RECSET.Open(sSQL, _
connectionString, , , _
CommandTypeEnum.adCmdText)

If Not RECSET.EOF Then

Call sheet.Range("a" & fila).CopyFromRecordset(RECSET)

Else

Call MsgBox("Error: No data found", vbCritical)

End If

With sheet.Range("5:5000")
.RowHeight = 15
End With

If (RECSET.State And ObjectStateEnum.adStateOpen) Then RECSET.Close
Set RECSET = Nothing

End Sub

Any idea that may help me???

thanks! bregards Santiago

Thanks Dave, worked perfectly. Can I ask why? I understood that I had to
insert "*" symbol.



"Dave Patrick" wrote:

Try

"SELECT * FROM [tblBASE] WHERE VESSEL LIKE 'MSC%' "

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"Santiago" wrote:
| Just to add some (maybe) useful info: if the sSQLstring is:
|
| sSQLString = "SELECT * FROM [tblBASE]"
|
| the query brings every record in the table, but cannot filter MSC* in the
| sql query...
|
| thanks.
|
| bregards
| Santiago
|
| "Santiago" wrote:
|
| I cannot make this query work...
|
| first I make the sql string like this:
|
| sSQLString = "SELECT * FROM [tblBASE] WHERE VESSEL LIKE 'MSC*' "
|
| Then I use this string in the following code, connecting to an Access
| database. I just want to retreive data of all records where vessel
starts
| with "MSC".
|
| Public Sub retrieveData(sSQL As String)
|
| Dim RECSET As ADODB.Recordset
| Dim connectionString As String
| Dim fila As Long
| Dim sheet As Worksheet
|
| Set sheet = ActiveSheet
|
|
| connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
| "Data Source=" & ActiveWorkbook.path & "\Tracking.mdb;"
|
| fila = 5
|
| Set RECSET = New ADODB.Recordset
| Call RECSET.Open(sSQL, _
| connectionString, , , _
| CommandTypeEnum.adCmdText)
|
| If Not RECSET.EOF Then
|
| Call sheet.Range("a" & fila).CopyFromRecordset(RECSET)
|
| Else
|
| Call MsgBox("Error: No data found", vbCritical)
|
| End If
|
| With sheet.Range("5:5000")
| .RowHeight = 15
| End With
|
| If (RECSET.State And ObjectStateEnum.adStateOpen) Then RECSET.Close
| Set RECSET = Nothing
|
| End Sub
|
| Any idea that may help me???
|
| thanks! bregards Santiago

Hah! I bet that's it!

:-)

Dave Patrick wrote:
Try

"SELECT * FROM [tblBASE] WHERE VESSEL LIKE 'MSC%' "

Thanks Gareth, but what Dave proposed worked. Instead of * I put % and worked.

FYI: I received "no records found" error with the * symbol...

rgds

Santiago

"Gareth" wrote:

What do you mean by "cannot make this query work"? Cab you be more specific?

Does this SQL string work in Access?

Do you get an error message in Excel or just no records returned? Since
your simpler query works I would suspect it is a problem on the Access
end rather than with VBA.

HTH
G

Santiago wrote:
Just to add some (maybe) useful info: if the sSQLstring is:

sSQLString = "SELECT * FROM [tblBASE]"

the query brings every record in the table, but cannot filter MSC* in the
sql query...

thanks.

bregards
Santiago

"Santiago" wrote:


I cannot make this query work...

first I make the sql string like this:

sSQLString = "SELECT * FROM [tblBASE] WHERE VESSEL LIKE 'MSC*' "

Then I use this string in the following code, connecting to an Access
database. I just want to retreive data of all records where vessel starts
with "MSC".

Public Sub retrieveData(sSQL As String)

Dim RECSET As ADODB.Recordset
Dim connectionString As String
Dim fila As Long
Dim sheet As Worksheet

Set sheet = ActiveSheet


connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ActiveWorkbook.path & "\Tracking.mdb;"

fila = 5

Set RECSET = New ADODB.Recordset
Call RECSET.Open(sSQL, _
connectionString, , , _
CommandTypeEnum.adCmdText)

If Not RECSET.EOF Then

Call sheet.Range("a" & fila).CopyFromRecordset(RECSET)

Else

Call MsgBox("Error: No data found", vbCritical)

End If

With sheet.Range("5:5000")
.RowHeight = 15
End With

If (RECSET.State And ObjectStateEnum.adStateOpen) Then RECSET.Close
Set RECSET = Nothing

End Sub

Any idea that may help me???

thanks! bregards Santiago

Continuing with the lessons... (sorry I'm new at ADO & SQL and I'm having
many headaches...)

I try to insert records into the tblBASE with SQL, but cannot make it work
either. Here's my code. Thanks guys!!

First I define the "sSQLstring" like this and call the next sub:

Dim sSQLString As String

sSQLString = "INSERT INTO tblBASE_SIDERCA (MILL, ORDER, ITEM, CUSTOMER,
SALE_COND, VESSEL, TYPE_ORDER, ORIGIN_PORT, ID_FINAL_DEST, FINAL_DESTINATION,
ID_EMB, OWNER, TONS, DEPARTURE_DATE, TT_TTS, REQ_DATE)"

Call uploadData(sSQLString)



and here's the uploadData sub...


Public Sub uploadData(sSQL As String)

Dim fila As Long
Dim Command As ADODB.Command
Dim sSQLvalues As String
Dim connectionString As String

fila = 5

connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ActiveWorkbook.path & "\Tracking.mdb;" '& _
"User ID=Admin;" & _
"Password=pass;"

Set Command = New ADODB.Command
Command.ActiveConnection = connectionString

Do While Range("A" & fila) < ""

sSQLvalues = sSQL & " VALUES ('" & Range("A" & fila) & "', '" & _
Range("B" & fila) & "', '" & _
Range("C" & fila) & "', '" & _
Range("D" & fila) & "', '" & _
Range("F" & fila) & "', '" & _
Range("G" & fila) & "', '" & _
Range("I" & fila) & "', '" & _
Range("J" & fila) & "', '" & _
Range("K" & fila) & "', '" & _
Range("L" & fila) & "', '" & _
Range("M" & fila) & "', '" & _
Range("O" & fila) & "', '" & _
Range("P" & fila) & "', '#" & _
Format(Range("U" & fila), "mm/dd/yy") & "#', '" & _
Range("AB" & fila) & "', '#" & _
Format(Range("AC" & fila), "mm/dd/yy") & "#')"

MsgBox sSQLvalues

Command.CommandText = sSQLvalues

Call Command.Execute(, , CommandTypeEnum.adCmdText)

fila = fila + 1
Loop

End Sub


THANKS MILLIONS FOR THE HELP!


"Gareth" wrote:

Hah! I bet that's it!

:-)

Dave Patrick wrote:
Try

"SELECT * FROM [tblBASE] WHERE VESSEL LIKE 'MSC%' "

For wildcard ADO requires that you use the percent sign %
It was DAO that uses *

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"Santiago" wrote:
| Thanks Dave, worked perfectly. Can I ask why? I understood that I had to
| insert "*" symbol.

This might be a help for getting data to and from Excel and Access: It
includes examples of using variables in SQL queries.
http://www.bygsoftware.com/examples/sql.html

Or you can get there from the "Excel with Access Databases" section on page:
http://www.bygsoftware.com/examples/examples.htm

It demonstrates how to use SQL in Excel's VBA to:

* create a database,
* create a table
* insert records
* select records,
* update records,
* delete records,
* delete a table,
* delete a database.

DAO and ADO files available.

You can also download the demonstration file called "excelsql.zip".

The code is open and commented.


--
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-

"Santiago" wrote in message
...
Continuing with the lessons... (sorry I'm new at ADO & SQL and I'm having
many headaches...)

I try to insert records into the tblBASE with SQL, but cannot make it work
either. Here's my code. Thanks guys!!

First I define the "sSQLstring" like this and call the next sub:

Dim sSQLString As String

sSQLString = "INSERT INTO tblBASE_SIDERCA (MILL, ORDER, ITEM,
CUSTOMER,
SALE_COND, VESSEL, TYPE_ORDER, ORIGIN_PORT, ID_FINAL_DEST,
FINAL_DESTINATION,
ID_EMB, OWNER, TONS, DEPARTURE_DATE, TT_TTS, REQ_DATE)"

Call uploadData(sSQLString)



and here's the uploadData sub...


Public Sub uploadData(sSQL As String)

Dim fila As Long
Dim Command As ADODB.Command
Dim sSQLvalues As String
Dim connectionString As String

fila = 5

connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ActiveWorkbook.path & "\Tracking.mdb;" '& _
"User ID=Admin;" & _
"Password=pass;"

Set Command = New ADODB.Command
Command.ActiveConnection = connectionString

Do While Range("A" & fila) < ""

sSQLvalues = sSQL & " VALUES ('" & Range("A" & fila) & "', '" & _
Range("B" & fila) & "', '" & _
Range("C" & fila) & "', '" & _
Range("D" & fila) & "', '" & _
Range("F" & fila) & "', '" & _
Range("G" & fila) & "', '" & _
Range("I" & fila) & "', '" & _
Range("J" & fila) & "', '" & _
Range("K" & fila) & "', '" & _
Range("L" & fila) & "', '" & _
Range("M" & fila) & "', '" & _
Range("O" & fila) & "', '" & _
Range("P" & fila) & "', '#" & _
Format(Range("U" & fila), "mm/dd/yy") & "#', '" & _
Range("AB" & fila) & "', '#" & _
Format(Range("AC" & fila), "mm/dd/yy") & "#')"

MsgBox sSQLvalues

Command.CommandText = sSQLvalues

Call Command.Execute(, , CommandTypeEnum.adCmdText)

fila = fila + 1
Loop

End Sub


THANKS MILLIONS FOR THE HELP!


"Gareth" wrote:

Hah! I bet that's it!

:-)

Dave Patrick wrote:
Try

"SELECT * FROM [tblBASE] WHERE VESSEL LIKE 'MSC%' "

INSERT INTO rewuires a list of the VALUES to be inserted as well.

sSQLString = "INSERT INTO tblBASE_SIDERCA (MILL, ORDER, ITEM, CUSTOMER,
SALE_COND, VESSEL, TYPE_ORDER, ORIGIN_PORT, ID_FINAL_DEST, FINAL_DESTINATION,
ID_EMB, OWNER, TONS, DEPARTURE_DATE, TT_TTS, REQ_DATE)"

sSQLString = SSQLString & " VALUES(" & valuelist & ")"

now valuelist is the data to be loaded/ number are ok but text needs to be
wrapped in single qiotes, eg
'abc',123,'def',3,'more text'

HTH








"Santiago" wrote:

Continuing with the lessons... (sorry I'm new at ADO & SQL and I'm having
many headaches...)

I try to insert records into the tblBASE with SQL, but cannot make it work
either. Here's my code. Thanks guys!!

First I define the "sSQLstring" like this and call the next sub:

Dim sSQLString As String

sSQLString = "INSERT INTO tblBASE_SIDERCA (MILL, ORDER, ITEM, CUSTOMER,
SALE_COND, VESSEL, TYPE_ORDER, ORIGIN_PORT, ID_FINAL_DEST, FINAL_DESTINATION,
ID_EMB, OWNER, TONS, DEPARTURE_DATE, TT_TTS, REQ_DATE)"

Call uploadData(sSQLString)



and here's the uploadData sub...


Public Sub uploadData(sSQL As String)

Dim fila As Long
Dim Command As ADODB.Command
Dim sSQLvalues As String
Dim connectionString As String

fila = 5

connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ActiveWorkbook.path & "\Tracking.mdb;" '& _
"User ID=Admin;" & _
"Password=pass;"

Set Command = New ADODB.Command
Command.ActiveConnection = connectionString

Do While Range("A" & fila) < ""

sSQLvalues = sSQL & " VALUES ('" & Range("A" & fila) & "', '" & _
Range("B" & fila) & "', '" & _
Range("C" & fila) & "', '" & _
Range("D" & fila) & "', '" & _
Range("F" & fila) & "', '" & _
Range("G" & fila) & "', '" & _
Range("I" & fila) & "', '" & _
Range("J" & fila) & "', '" & _
Range("K" & fila) & "', '" & _
Range("L" & fila) & "', '" & _
Range("M" & fila) & "', '" & _
Range("O" & fila) & "', '" & _
Range("P" & fila) & "', '#" & _
Format(Range("U" & fila), "mm/dd/yy") & "#', '" & _
Range("AB" & fila) & "', '#" & _
Format(Range("AC" & fila), "mm/dd/yy") & "#')"

MsgBox sSQLvalues

Command.CommandText = sSQLvalues

Call Command.Execute(, , CommandTypeEnum.adCmdText)

fila = fila + 1
Loop

End Sub


THANKS MILLIONS FOR THE HELP!


"Gareth" wrote:

Hah! I bet that's it!

:-)

Dave Patrick wrote:
Try

"SELECT * FROM [tblBASE] WHERE VESSEL LIKE 'MSC%' "