Could some one please help me to interpret the following syntax

  • Thread starter Thread starter Augustus
  • Start date Start date
A

Augustus

I am asked to reprogram a Excel/VBA program, and need help
regarding the following code which querying an Access
Database on the server:


Set Conn = CreateObject("ADODB.Connection")
Conn.Open strADOName
strSQL = "SELECT JobNo FROM tblPourItem"
strSQL = strSQL & " WHERE JobNo = '" & strJobNo & "'"


Could someone please direct me to a source where I could
get information relating to querying external DataBase,
like in the above example, what's the function of SELECT?

Especially this line:
strSQL = strSQL & " WHERE JobNo = '" & strJobNo & "'"
WHat is the purpose of ' ?

Also, is there a function in ADO that would let me
retrieve the file's path? Cause I need to find out the
date last modified to Conn(in the example, which is
located on the sever).
 
This probably isn't the best newsgroup to be asking about SQL.

Since I know a fair bit about SQL, I'll try to answer what I can.
Im getting simple about it. SQL is a huge topic. There are books with >1,000
pages which don't cover everything.

SQL stands for Structured Query Language.
There are 4 ways to work with data: Select, Insert, Update, Delete.

The syntax of a basic Select statement goes a little like:
Select <column name>
From <table/view>
Where <criteria>

As an example, your SQL statement criteria could evaluate to:
JobNo = '12345'

Strings are usually enclosed by single quotes, depending on the database.

Note that most databases are not made up as a single MDB file.
ADO acts as a standard interface to a variety of databases - those which
provide support for OLEDB.

Given it's an Access database, it's quite likely you'll find the file
location as a property of the Connection object. It might even be in the
strADOName variable.
 
Hi Augustus,

Your question is so basic that I wonder if you would be better advised to go
an take an SQL course, read a good boo on SQL, or even employ a programmer
to do it for you, it might save a lot of grief.

On the second point, here is some code to get the last modified date of a
file

Dim FSO As Object
Dim oFile As Object
Dim sfn As String

sfn = InputBox("Supply full file name")
If sfn <> "" Then
Set FSO = CreateObject("Scripting.FileSystemObject")
On Error GoTo no_file
Set oFile = FSO.GetFile(sfn)
MsgBox sfn & " " & Format(oFile.DateLAstModified, "dd mmm yyyy")
Exit Sub

no_file:
MsgBox sfn & " cannot be found"
End If

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Thanks, first of all.

I didn't know it is a SQL thing until today (i figure out
lat night that the strSQL is passing a string to
somewhere).

My skill at VBA is very limited. i have done alot of
programming.
Could either of you please tell me how do you know that it
is SQL, and not other form like Jet or ODB?

Please give me a bried explaination on SQL, if you don't
mind?
Thanks
Augustus
 
Augustus,

There are a number of bits that you did not give us.

This simply creates an ADO connection object.

This is where the connection is opened. As you can see, it uses a variable
strADOName, which is what are called the connection parameters, (server,
data source, id, password, etc). Somewhere in your code this muse be
defined, and this should be easily checked.

This is a very simple SQL statement that queries the table tblPourItem, and
retrieves the JobNo column, but only for rows where the JobNo equals the
value in the variable strJobNo (again defined somewhere in your code).

What else do you want to know? Don't expect us to be able to teach you SQL
over the NG, just not feasible.


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top