Hi,
There are two small errors in your code. First, CurrentDB is some sort
of short hand for DBEngine(0)(0) (not exactly but I don't mind the details)
under JET and is therefore unavalaible under ADP. In your code, CurrentDB
simply return nothing.
Second, you should specify db as DAO.Database or as ADODB.Database;
otherwise Access will take the first definition that you have set up in your
references; which will give the wrong one one out of two.
With some editing:
Dim db As dao.Database, rs As dao.Recordset, strSQL As String, myResult
As String
Set db = DBEngine.OpenDatabase("c:\db4.mdb")
strSQL = "select Criteres.IdCritere from Criteres"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
myResult = rs("IdCritere")
MsgBox myResult
But under ADP, it is often preferable to forget about DAO and use OLEDB
instead. You can open directly an MDB database with OLEDB in VBA code like
you would with any VB or ASP code by using the proper connection string.
(You can find numerous details about connection strings at
http://www.able-consulting.com/ADO_Conn.htm . Don't forget to use pure
OLEDB preferably over ODBC or ODBC-OLEDB.) You will find exemples about
this everywhere in books on OLEDB and on the internet. You can also
CurrentProject.Connection to open you connection instead of CreateObject ().
Here a short recall:
Dim cnn as ADODB.Connection
Set cnn = New ADOBDB.Connection
cnn.open "Provider=Microsoft.Jet.OLEDB.4.0;Data
source=C:\db4.mdb;User ID=Admin;Password="
...
But for transfering data directly from MDB to SQL-Server, without using many
connections and recordsets, you can open a direct DAO connection to your
SQL-Server :
Public Function DAODatabase() As dao.Database
Dim cnn As ADODB.Connection
Dim dbDAO As dao.Database
Dim strConnect As String
Set cnn = CurrentProject.Connection
' Is the connection based on MSdataShape- or SQLOLEDB-provider?
If InStr(cnn.Provider, "Microsoft.Access.OLEDB") > 0 Or
InStr(cnn.Provider, "MSDataShape") > 0 Or InStr(cnn.Provider, "SQLOLEDB") >
0 Then
' Build connection string
strConnect = "ODBC;driver=SQL Server;server=" & cnn.Properties("Data
Source") & ";"
' Database Name
strConnect = strConnect & "database=" & cnn.Properties("Initial
Catalog") & ";"
' SQL Server- or Windows-security?
If cnn.Properties("Integrated Security") = "SSPI" Then
strConnect = strConnect & "Trusted_Connection=Yes;"
Else
strConnect = strConnect & "UID=" & cnn.Properties("User ID") &
";"
strConnect = strConnect & "PWD=" & cnn.Properties("Password") &
";"
End If
Else
MsgBox "DAO-Database not opened!"
Set DAODatabase = Nothing
Exit Function
End If
' Open Database
Set dbDAO = DBEngine.OpenDatabase("", False, False, strConnect)
Set DAODatabase = dbDAO
End Function
And use it:
Dim rs As dao.Recordset
Dim db As dao.Database
Set db = DAODatabase()
strSQL = "select Criteres.IdCritere from Criteres in 'c:\db4.mdb'"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
myResult = rs("IdCritere")
Or for transfering data:
db.execute "select into Criteres select * From Criteres in
'c:\db4.mdb' ", dbFailOnError Or dbSeeChanges
Buying a good book, like "Microsoft Access Projects with Microsoft SQL
Server", Ralf Albrecht and Natascha Nicol, Microsoft Press, will surely save
you a lot of time.
S. L.
Lauren Quantrell said:
Sylvain,
How would I duplicate this code from a DAO database to use the .MDB
table data in my Access2000 ADP database then???
Dim db As Database, rs As DAO.Recordset, strSQL As String,
myResult As String
Set db = CurrentDb()
strSQL = "select tblTableName.fld1 from tblTableName in
'C:\Program Files\FolderName\DatabaseName.mdb'"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
myResult = rs("fld1")
MsgBox myResult
Your help is appreciated,
lq
"Sylvain Lafontaine" <sylvain@_NO_SPAM_aei.ca> wrote in message
There are no option "Link tables..." under the "Import" option? Funny,
this option is there on my version of Access 2002. Probably it didn't exist
with Access 2000 or that you didn't make a full installation.
Your only option then is to use the "in" syntaxe with proper vba code. This
is what I usually use personnally when I want to manipulate MDB from ADP.
For exemple (notice the keyword "in") :
select * from table1 in 'C:\LigueXpert2000.mdb'
And, of course, when there is blank spaces in the name of the table:
select * from [my table1] in 'C:\LigueXpert2000.mdb'
I have not made a full investigation, but usually you can use ODBC or OLEDB
connection string, something like:
select * from table1 in 'ODBC;DSN=MyDatabase'
S. L.
Lauren Quantrell said:
Sylvian,
For my database I am using Access2000 as an .ADP file connected to the
SQL Server backend. Under FILE>GET EXTERNAL DATA the only option is
IMPORT. There is no option to get external data and link some local
.MDB tables to my .ADP project.
Any help is appreciated.
lq
"Sylvain Lafontaine" <sylvain@_NO_SPAM_aei.ca> wrote in message
From the File menu, chose "Get External Data", then "Link tables..." and
finally "Transact SQL". Select the MDB database and the tables.
But there seem to have a problem, as the given link is read-only. I don't
know why at the moment. See my other post for the syntaxe that I
use in
VBA
to have read/write operation in VBA on mdb database from ADP.
S. L.
How do you link an Access local table to an ADP?
I thought is was SQL Server tables only through the connection?
LQ
"TF" <tf85@@hotmail.com> wrote in message
Thank you for your idea,
I'll tried to use a local mdb application (as temporary tables).
But an Access linked table becomes a view in my ADP project ??
WHY ?
"Sylvain Lafontaine" <sylvain@_NO_SPAM_aei.ca> a écrit dans le message
de
I'm not sure about your exact needs for a temporary table, but usually
you
can replace it with a permanent table if the user can only see
his
own
data.
Add a new field to the table wich will contain the UID or the
USER
ID
of
the
user and use it to distinguate beetween your users when you
make
an
insert,
a select, a delete, an update or a view of your table; so that each
user
will only see its own data.
Of course, you can take whatever you want for this added
field.
It
can
even
be an autoincremental value coming from an auxiliary table.
If
you
add a
date value to this second table, you can easily implement a
clean
up
fonction for old data wich have not been deleted for whatever reason.
Also, when you are using temporary values to be acceded
outside
the
SQL
Server (outside of stored procedure), directly by your Access application,
you must take care of the necessary network bandwidth. Maybe
the
use
of a
disconnected recordset or a real local table stored in an auxiliary
MDB
database will be more useful in many situations but thats
depend
on
your
exact need; which I don't know.
S. L.
The temporary table must be visible only by the user which create
this
table
?
"Sylvain Lafontaine" <sylvain@_NO_SPAM_aei.ca> a écrit dans le
message
de
Temporary tables are to be used inside stored procedures.
If you want to call them directly from Access, use permanent
tables
instead
with an added field to discriminate beetween users. A
little
less
convenient then true temporary tables, but will wok perfectly
inside
Access.
You could also use a local mdb database to do your work
from
inside
the
ADP.
This will save on the network bandwith.
S. L.
Hello,
In MS Acces Application front end, I used local tables
to do
some
works.
In
ADP project, I can't do it; So I have to use temporary tables.
I would like use this kind of table during the ADP connection.
Can I do it with the term : create table ##Tab ....... ?
or we have another kind of temporary tables ?
Thanks for all
