ADO & SQL

S

Santiago

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
 
S

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
 
D

Dave Patrick

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

:
| 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
 
G

Gareth

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
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

:

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
Click to expand...
Click to expand...
 
S

Santiago

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

Santiago

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 said:
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
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

:

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
Click to expand...
Click to expand...
Click to expand...
 
S

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 said:
Hah! I bet that's it!

:)

Dave said:
Try

"SELECT * FROM [tblBASE] WHERE VESSEL LIKE 'MSC%' "
Click to expand...
Click to expand...
 
D

Dave Patrick

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

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

Andy Wiggins

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 said:
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 said:
Hah! I bet that's it!

:)

Dave said:
Try

"SELECT * FROM [tblBASE] WHERE VESSEL LIKE 'MSC%' "
Click to expand...
Click to expand...
Click to expand...
 
P

Patrick Molloy

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 said:
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 said:
Hah! I bet that's it!

:)

Dave said:
Try

"SELECT * FROM [tblBASE] WHERE VESSEL LIKE 'MSC%' "
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

ADO connection Problems between excel worbooks 0
ADO & Loops: error in code? 2
ADO, pls help! 3
ADO wont update table? 1
ADO / SQL / 0
Simple database questions - Export query output to a sheet. 0
How to populate VBA Variables from ADODB.Connection Results? 4
OWC (office web component ) chart , stacked XY scatter 0

Top