By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,490 Members | 1,753 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,490 IT Pros & Developers. It's quick & easy.

Select data from other DB

P: n/a
bsn
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
Feb 18 '06 #1
Share this Question
Share on Google+
10 Replies


P: n/a
"bsn" <bsnsnabelaoncabledotdk> wrote in message
news:43***********************@dread12.news.tele.d k...
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;DATABASE=C:\Test.mdb]

However, if not, you could also use a simpler form:
SELECT * FROM MyTable IN "C:\Test.mdb"
Feb 18 '06 #2

P: n/a
bsn

"Anthony England" <ae******@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;DATABASE=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\Wl2kdata.mdb"

Bjarne
Feb 18 '06 #3

P: n/a
bsn

"Anthony England" <ae******@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;DATABASE=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

Feb 18 '06 #4

P: n/a
"bsn" <bsnsnabelaoncabledotdk> wrote in message
news:43***********************@dread12.news.tele.d k...

"Anthony England" <ae******@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;DATABASE=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

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:\Documents 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:\Documents 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

Feb 18 '06 #5

P: n/a
bsn

"Anthony England" <ae******@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
Feb 18 '06 #6

P: n/a
"Anthony England" <ae******@oops.co.uk> wrote in
news:dt**********@nwrdmz02.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/
Feb 18 '06 #7

P: n/a

"David W. Fenton" <XX*******@dfenton.com.invalid> wrote in message
news:Xn**********************************@127.0.0. 1...
"Anthony England" <ae******@oops.co.uk> wrote in
news:dt**********@nwrdmz02.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/

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'

Feb 19 '06 #8

P: n/a
bsn

"Anthony England" <ae******@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
Feb 19 '06 #9

P: n/a
"Anthony England" <ae******@oops.co.uk> wrote in
news:dt**********@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com:
"David W. Fenton" <XX*******@dfenton.com.invalid> wrote in message
news:Xn**********************************@127.0.0. 1...
"Anthony England" <ae******@oops.co.uk> wrote in
news:dt**********@nwrdmz02.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)?


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/
Feb 19 '06 #10

P: n/a

"David W. Fenton" <XX*******@dfenton.com.invalid> wrote in message
news:Xn**********************************@127.0.0. 1...
"Anthony England" <ae******@oops.co.uk> wrote in
news:dt**********@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com:
"David W. Fenton" <XX*******@dfenton.com.invalid> wrote in message
news:Xn**********************************@127.0.0. 1...
"Anthony England" <ae******@oops.co.uk> wrote in
news:dt**********@nwrdmz02.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)?


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/

Indeed. And here I would have no objection. But I don't always like the
way my queries are adjusted with the query editor.
Feb 19 '06 #11

This discussion thread is closed

Replies have been disabled for this discussion.