SQL statement in VBA

[killeronloose via AccessMonster.com]

Hi,

I want to use this SQL code in VBA.

SELECT Currencies.*, Countries.*
FROM Countries RIGHT JOIN Currencies ON Countries.[Country ID] = Currencies.
[Country ID];

Help appreciated on correct syntax.

Thanks.

 

[Stefan Hoffmann]

hi,

I want to use this SQL code in VBA.

What do you like to do with it?

imho the most common scenario:

Public Sub Foo()

Dim rs As DAO.Recordset

Dim sql As String

sql = "SELECT Cu.*, Co.* " & _
"FROM Countries Co " & _
"RIGHT JOIN Currencies Cu " & _
"ON Co.[Country ID] = Cu.[Country ID];"

Set rs = CurrentDb.OpenRecordset(sql, dbOpenSnapshot)
' Do something with rs, e.g.:
MsgBox "Found " & rs.RecordCount & " records."
rs.Close
Set rs = Nothing

End Sub

mfG
--> stefan <--

 

[killeronloose via AccessMonster.com]

I have this query in an accdb file. I am looking to create this query through
VBA (CreateQueryDef), but can't seem to get correct syntax.

hi,

I want to use this SQL code in VBA.

What do you like to do with it?

imho the most common scenario:

Public Sub Foo()

Dim rs As DAO.Recordset

Dim sql As String

sql = "SELECT Cu.*, Co.* " & _
"FROM Countries Co " & _
"RIGHT JOIN Currencies Cu " & _
"ON Co.[Country ID] = Cu.[Country ID];"

Set rs = CurrentDb.OpenRecordset(sql, dbOpenSnapshot)
' Do something with rs, e.g.:
MsgBox "Found " & rs.RecordCount & " records."
rs.Close
Set rs = Nothing

End Sub

mfG
--> stefan <--

 

[Stefan Hoffmann]

hi,

I have this query in an accdb file. I am looking to create this query through
VBA (CreateQueryDef), but can't seem to get correct syntax.

hmm, providing a concise and complete example would have been nice...

Option Compare Database
Option Explicit

Public Sub CreateQuery(AQueryName As String)

On Local Error Resume Next

Dim db As DAO.Database
Dim qd As DAO.QueryDef

Dim errorNumber As Long
Dim sql As String

Set db = CurrentDb
Set qd = db.QueryDefs.Item(AQueryName)
errorNumber = Err.Number

On Local Error GoTo LocalError

sql = "SELECT * " & _
"FROM Countries AS Co " & _
"RIGHT JOIN Currencies AS Cu " & _
"ON Co.[Country ID] = Cu.[Country ID];"

If errorNumber = 3265 Then
' Query does not exist, create it.
db.CreateQueryDef AQueryName, sql
Else
' Otherwise modify the existing one.
qd.sql = sql
End If
db.QueryDefs.Refresh

Set qd = Nothing
Set db = Nothing

Exit Sub

LocalError:
MsgBox Err.Description, vbCritical

End Sub

btw, you should avoid

a) using special characters in field and/orobject names.
b) naming tables using pluralization.

mfG
--> stefan <--

 

[vanderghast]

You don't need to have a saved query in order to execute it, you can very
well execute an ad hoc query, but here is another (simpler?) way to create a
query:

CurrentProject.Connection.Execute "CREATE PROC queryNameYouWant AS SELECT
Currencies.*, Countries.* FROM Countries RIGHT JOIN Currencies ON
Countries.Country ID] = Currencies.[Country ID]; "


(note that is it only one line.)


It requires Jet 4.0 (since we CREATE a stored PROCedure without parameter)


Vanderghast, Access MVP

 

[killeronloose via AccessMonster.com]

I have two tables - Currencies and Countries.

Countries have two fields - [Country ID] and Name
Currencies have three fields - ID, Name and [Country ID].

I have to make a form to insert data in Currencies table. Since the user will
have to choose [Country ID] by data sourced from Countries table, I decided
to make a query by joining the tables and then make the form based on this
query.

SELECT Currencies.*, Countries.*
FROM Countries RIGHT JOIN Currencies ON Countries.[Country ID] = Currencies.
[Country ID];

While I am able to make query through query design, I am unable to do so from
VBA. The error "3131: Syntax error in FROM clause" appears no matter what I
try.

hi,

I have this query in an accdb file. I am looking to create this query through
VBA (CreateQueryDef), but can't seem to get correct syntax.

hmm, providing a concise and complete example would have been nice...

Option Compare Database
Option Explicit

Public Sub CreateQuery(AQueryName As String)

On Local Error Resume Next

Dim db As DAO.Database
Dim qd As DAO.QueryDef

Dim errorNumber As Long
Dim sql As String

Set db = CurrentDb
Set qd = db.QueryDefs.Item(AQueryName)
errorNumber = Err.Number

On Local Error GoTo LocalError

sql = "SELECT * " & _
"FROM Countries AS Co " & _
"RIGHT JOIN Currencies AS Cu " & _
"ON Co.[Country ID] = Cu.[Country ID];"

If errorNumber = 3265 Then
' Query does not exist, create it.
db.CreateQueryDef AQueryName, sql
Else
' Otherwise modify the existing one.
qd.sql = sql
End If
db.QueryDefs.Refresh

Set qd = Nothing
Set db = Nothing

Exit Sub

LocalError:
MsgBox Err.Description, vbCritical

End Sub

btw, you should avoid

a) using special characters in field and/orobject names.
b) naming tables using pluralization.

mfG
--> stefan <--

 

[Stuart McCall]

I have two tables - Currencies and Countries.

Countries have two fields - [Country ID] and Name
Currencies have three fields - ID, Name and [Country ID].

I have to make a form to insert data in Currencies table. Since the user
will
have to choose [Country ID] by data sourced from Countries table, I
decided
to make a query by joining the tables and then make the form based on this
query.

SELECT Currencies.*, Countries.*
FROM Countries RIGHT JOIN Currencies ON Countries.[Country ID] =
Currencies.
[Country ID];

While I am able to make query through query design, I am unable to do so
from
VBA. The error "3131: Syntax error in FROM clause" appears no matter what
I
try.

hi,

I have this query in an accdb file. I am looking to create this query
through
VBA (CreateQueryDef), but can't seem to get correct syntax.

hmm, providing a concise and complete example would have been nice...

Option Compare Database
Option Explicit

Public Sub CreateQuery(AQueryName As String)

On Local Error Resume Next

Dim db As DAO.Database
Dim qd As DAO.QueryDef

Dim errorNumber As Long
Dim sql As String

Set db = CurrentDb
Set qd = db.QueryDefs.Item(AQueryName)
errorNumber = Err.Number

On Local Error GoTo LocalError

sql = "SELECT * " & _
"FROM Countries AS Co " & _
"RIGHT JOIN Currencies AS Cu " & _
"ON Co.[Country ID] = Cu.[Country ID];"

If errorNumber = 3265 Then
' Query does not exist, create it.
db.CreateQueryDef AQueryName, sql
Else
' Otherwise modify the existing one.
qd.sql = sql
End If
db.QueryDefs.Refresh

Set qd = Nothing
Set db = Nothing

Exit Sub

LocalError:
MsgBox Err.Description, vbCritical

End Sub

btw, you should avoid

a) using special characters in field and/orobject names.
b) naming tables using pluralization.

mfG
--> stefan <--

Dim s As String

s = "SELECT Currencies.*,"
s = s & "Countries.*"
s = s & " FROM Countries"
s = s & " RIGHT JOIN Currencies"
s = s & " ON Countries.[Country ID] = Currencies."
s = s & " [Country ID];"

 

[killeronloose via AccessMonster.com]

Done. Thanks to everyone.

I have two tables - Currencies and Countries.

[quoted text clipped - 76 lines]
Dim s As String

s = "SELECT Currencies.*,"
s = s & "Countries.*"
s = s & " FROM Countries"
s = s & " RIGHT JOIN Currencies"
s = s & " ON Countries.[Country ID] = Currencies."
s = s & " [Country ID];"