|
Home > Archive > MS Access database support > February 2006 > Select data from other DB
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Select data from other DB
|
|
|
| Hello NG
I want to select data from another DB, and use this data in currentDB.
I have this SQL in a VBA procedure:
Sql = "SELECT * FROM Firma [MS Access;Database=" & strPath & strFileName &
";]"
It is not working
Any suggestions...
Thanks in advance...
Bjarne
| |
| Anthony England 2006-02-18, 9:23 am |
| "bsn" < bsnsnabelaoncabledot
dk> wrote in message
news:43f6d30d$0$3863
5$edfadb0f@dread12.news.tele.dk...
> Hello NG
> I want to select data from another DB, and use this data in currentDB.
> I have this SQL in a VBA procedure:
> Sql = "SELECT * FROM Firma [MS Access;Database=" & strPath & strFileName &
> ";]"
>
> It is not working
>
> Any suggestions...
>
> Thanks in advance...
>
> Bjarne
The syntax is not quite right, you are missing the IN ""
This format is useful if you needed to set other attributes, such as a
password, then you need to end up with something like:
SELECT * FROM MyTable IN "" [MS Access;PWD=secret;DA
TABASE=C:\Test.mdb]
However, if not, you could also use a simpler form:
SELECT * FROM MyTable IN "C:\Test.mdb"
| |
|
|
"Anthony England" <aengland@oops.co.uk> skrev
> The syntax is not quite right, you are missing the IN ""
> This format is useful if you needed to set other attributes, such as a
> password, then you need to end up with something like:
> SELECT * FROM MyTable IN "" [MS Access;PWD=secret;DA
TABASE=C:\Test.mdb]
>
> However, if not, you could also use a simpler form:
> SELECT * FROM MyTable IN "C:\Test.mdb"
Thank you...I tried this SQL, but it is not working...
SELECT * FROM Firma IN "C:\Documents and Settings\Bjarne S.
Nielsen\Skrivebord\W
l2kdata.mdb"
Bjarne
| |
|
|
"Anthony England" <aengland@oops.co.uk>
> The syntax is not quite right, you are missing the IN ""
> This format is useful if you needed to set other attributes, such as a
> password, then you need to end up with something like:
> SELECT * FROM MyTable IN "" [MS Access;PWD=secret;DA
TABASE=C:\Test.mdb]
If I try to run the sql in QBE, then it goes well, but not in my VBA code:
Sql = "SELECT * FROM Firma IN """" [MS Access;DATABASE=" & strPath &
strFileName & ";]"
RS.Open Sql
Debug.Print Rs!FirmaID
Bjarne
| |
| Anthony England 2006-02-18, 9:23 am |
| "bsn" < bsnsnabelaoncabledot
dk> wrote in message
news:43f71338$0$3865
9$edfadb0f@dread12.news.tele.dk...
>
> "Anthony England" <aengland@oops.co.uk>
> If I try to run the sql in QBE, then it goes well, but not in my VBA code:
>
> Sql = "SELECT * FROM Firma IN """" [MS Access;DATABASE=" & strPath &
> strFileName & ";]"
> RS.Open Sql
> Debug.Print Rs!FirmaID
>
> Bjarne
Well perhaps the mistake is elsewhere in the code. Try opening a recordset
for a normal local table, does it work? I guess because you call rs.open
you are coding using the ADO object library, but in any case you need to
show a full bit of code. Here are two examples - one using DAO and the
other ADO:
Public Sub DAO_Test()
On Error GoTo Err_Handler
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim lngCount As Long
Set dbs = CurrentDb
' strSQL = "SELECT * FROM tblContacts IN ""C:\Documents and
Settings\Test.mdb"""
strSQL = "SELECT * FROM tblContacts IN """" [MS Access;" & _
" DATABASE=C:\Document
s and Settings\Test.mdb]"
Set rst = dbs. OpenRecordset(strSQL
, dbOpenForwardOnly, dbReadOnly)
While Not rst.EOF
lngCount = lngCount + 1
rst.MoveNext
Wend
MsgBox "There are " & CStr(lngCount) & " records.", vbInformation
Exit_Handler:
If Not rst Is Nothing Then
rst.Close
Set rst = Nothing
End If
If Not dbs Is Nothing Then
Set dbs = Nothing
End If
Exit Sub
Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler
End Sub
Public Sub ADO_Test()
On Error GoTo Err_Handler
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim lngCount As Long
' strSQL = "SELECT * FROM tblContacts IN ""C:\Documents and
Settings\Test.mdb"""
strSQL = "SELECT * FROM tblContacts IN """" [MS Access;" & _
" DATABASE=C:\Document
s and Settings\Test.mdb]"
Set rst = New ADODB.Recordset
rst.Open strSQL, CurrentProject.Connection
While Not rst.EOF
lngCount = lngCount + 1
rst.MoveNext
Wend
MsgBox "There are " & CStr(lngCount) & " records.", vbInformation
Exit_Handler:
If Not rst Is Nothing Then
If rst.State > adStateOpen Then
rst.Close
End If
Set rst = Nothing
End If
Exit Sub
Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler
End Sub
| |
|
|
"Anthony England" <aengland@oops.co.uk> skrev
> rst.Open strSQL, CurrentProject.Connection
Works like a charme now...I forgot to set connection correct...
Thank you very much...
Bjarne
| |
| David W. Fenton 2006-02-18, 8:24 pm |
| "Anthony England" <aengland@oops.co.uk> wrote in
news:dt707b$2hu$1@nw
rdmz02.dmz.ncs.ea.ibs-infra.bt.com:
> However, if not, you could also use a simpler form:
> SELECT * FROM MyTable IN "C:\Test.mdb"
My copies of Access use single quotes for this. Is that an
internationalization
issue (I'm on US settings)?
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
| |
| Anthony England 2006-02-18, 8:24 pm |
|
"David W. Fenton" <XXXusenet@dfenton.com.invalid> wrote in message
news:Xns976EB02F17A4
Bf99a49ed1d0c49c5bbb
2@127.0.0.1...
> "Anthony England" <aengland@oops.co.uk> wrote in
> news:dt707b$2hu$1@nw
rdmz02.dmz.ncs.ea.ibs-infra.bt.com:
>
>
> My copies of Access use single quotes for this. Is that an
> internationalization
issue (I'm on US settings)?
>
> --
> David W. Fenton http://www.dfenton.com/
> usenet at dfenton dot com http://www.dfenton.com/DFA/
My settings are UK, but I don't believe that matters. I think either single
or double quotes are acceptable to JET but let us know if it doesn't on your
machine.
I also tested selecting Tools>Options>Tables/Queries and choosing SQL Server
Compatible Syntax (ANSI 92) then a query like
SELECT * FROM Contact WHERE Surname LIKE "Smith*"
does not work, I have to use:
SELECT * FROM Contact WHERE Surname LIKE "Smith%"
However, with my tests so far either within saved queries or via vba, and
regardless of settings, either single or double seem to be fine
SELECT * FROM Contact WHERE Surname="Smith"
SELECT * FROM Contact WHERE Surname='Smith'
| |
|
|
"Anthony England" <aengland@oops.co.uk> skrev
> My settings are UK, but I don't believe that matters. I think either
> single or double quotes are acceptable to JET but let us know if it
> doesn't on your machine.
My settings are DK - both quotes acceptable to JET.
Bjarne
| |
| David W. Fenton 2006-02-19, 8:38 pm |
| "Anthony England" <aengland@oops.co.uk> wrote in
news:dt8d4v$p9j$1@nw
rdmz03.dmz.ncs.ea.ibs-infra.bt.com:
> "David W. Fenton" <XXXusenet@dfenton.com.invalid> wrote in message
> news:Xns976EB02F17A4
Bf99a49ed1d0c49c5bbb
2@127.0.0.1...
>
> My settings are UK, but I don't believe that matters. I think
> either single or double quotes are acceptable to JET but let us
> know if it doesn't on your machine.
>
> I also tested selecting Tools>Options>Tables/Queries and choosing
> SQL Server Compatible Syntax (ANSI 92) then a query like
> SELECT * FROM Contact WHERE Surname LIKE "Smith*"
> does not work, I have to use:
> SELECT * FROM Contact WHERE Surname LIKE "Smith%"
>
> However, with my tests so far either within saved queries or via
> vba, and regardless of settings, either single or double seem to
> be fine SELECT * FROM Contact WHERE Surname="Smith"
> SELECT * FROM Contact WHERE Surname='Smith'
When you set a source in a different MDB in the query properties
Windows, the QBE writes the SQL with a single quote, so that seems
to suggest to me that the single quote is preferred.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
| |
| Anthony England 2006-02-19, 8:38 pm |
|
"David W. Fenton" <XXXusenet@dfenton.com.invalid> wrote in message
news:Xns976FA2577B3C
Cf99a49ed1d0c49c5bbb
2@127.0.0.1...
> "Anthony England" <aengland@oops.co.uk> wrote in
> news:dt8d4v$p9j$1@nw
rdmz03.dmz.ncs.ea.ibs-infra.bt.com:
>
>
> When you set a source in a different MDB in the query properties
> Windows, the QBE writes the SQL with a single quote, so that seems
> to suggest to me that the single quote is preferred.
>
> --
> David W. Fenton http://www.dfenton.com/
> usenet at dfenton dot com http://www.dfenton.com/DFA/
Indeed. And here I would have no objection. But I don't always like the
way my queries are adjusted with the query editor.
|
|
|
|
|