|
|
Hello,
I give up try to figure out why I can't have the Access 2003 module open an
ODBC connection to a System DSN that is setup and works otherwise.
Here's what the help says:
"A String that is the name of an existing Microsoft Jet database file, or
the data source name (DSN) of an ODBC data source. See the Name property for
more information about setting this value."
Specifically it says: "or the data source name (DSN) of an ODBC data source"
If I add the DSN name to the dbname field then it just says that it Can't
find the file. The only way it seems to at least do something is to use ""
as the dbname and put "DSN=dnsnamehere" in the connect field. What happens
is it pops up the ODBC selection dialog and I have to manually click on
System DSN then double click the item in the list.
Basically this is where I left it (and yes dnsname gets set to the correct
dsn name):
Option Compare Database
Public Function AddIP(tablename As String, ip As String)
Dim db As Database
Dim rs As Recordset
Dim dsnname As String
If ip = "" Then Exit Function
dsnname = DLookup("[IPDSN]", "Settings", "[id]=1")
Set db = OpenDatabase("", dbDriverNoPrompt, , "ODBC;DSN=" & dsnname)
|
|
Your error is because you have forgot to give the value for the third
argument. This third argument is not by default when you also give a fourth
argument; so you should try with:
Set db = OpenDatabase("", dbDriverNoPrompt, False, "ODBC;DSN=" &
dsnname)
Also, make sure that you give the correct UID and PWD is those are required
for the backend database. Finally, it's never a bad idea to explicitely set
db and rs to their full type:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim dsnname As String
If ip = "" Then Exit Function
dsnname = DLookup("[IPDSN]", "Settings", "[id]=1")
Set db = DBEngine.OpenDatabase("", dbDriverNoPrompt, False, "ODBC;DSN="
& dsnname)
Finally, you cannot open a database on all types of ODBC driver. For
exemple, while you can to this with SQL-Server, I'm not sure you could with
JET and many other ISAM databases. Instead of opening a database, you
should use ODBC Linked Tables.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"David F." <df2705[ at ]community.nospam> wrote in message
news:u4$OrIDmHHA.596[ at ]TK2MSFTNGP06.phx.gbl...
[Quoted Text] > Hello,
>
> I give up try to figure out why I can't have the Access 2003 module open
> an ODBC connection to a System DSN that is setup and works otherwise.
>
> Here's what the help says:
>
> "A String that is the name of an existing Microsoft Jet database file, or
> the data source name (DSN) of an ODBC data source. See the Name property
> for more information about setting this value."
>
> Specifically it says: "or the data source name (DSN) of an ODBC data
> source"
>
> If I add the DSN name to the dbname field then it just says that it Can't
> find the file. The only way it seems to at least do something is to use
> "" as the dbname and put "DSN=dnsnamehere" in the connect field. What
> happens is it pops up the ODBC selection dialog and I have to manually
> click on System DSN then double click the item in the list.
>
> Basically this is where I left it (and yes dnsname gets set to the correct
> dsn name):
>
> Option Compare Database
>
> Public Function AddIP(tablename As String, ip As String)
> Dim db As Database
> Dim rs As Recordset
> Dim dsnname As String
>
> If ip = "" Then Exit Function
> dsnname = DLookup("[IPDSN]", "Settings", "[id]=1")
>
> Set db = OpenDatabase("", dbDriverNoPrompt, , "ODBC;DSN=" & dsnname)
>
>
>
|
|
Yes it worked! Thanks.
Argh.
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:%23C33uPLmHHA.4848[ at ]TK2MSFTNGP05.phx.gbl...
[Quoted Text] > Your error is because you have forgot to give the value for the third
> argument. This third argument is not by default when you also give a
> fourth argument; so you should try with:
>
> Set db = OpenDatabase("", dbDriverNoPrompt, False, "ODBC;DSN=" &
> dsnname)
>
> Also, make sure that you give the correct UID and PWD is those are
> required for the backend database. Finally, it's never a bad idea to
> explicitely set db and rs to their full type:
>
> Dim db As DAO.Database
> Dim rs As DAO.Recordset
> Dim dsnname As String
>
> If ip = "" Then Exit Function
> dsnname = DLookup("[IPDSN]", "Settings", "[id]=1")
>
> Set db = DBEngine.OpenDatabase("", dbDriverNoPrompt, False, "ODBC;DSN="
> & dsnname)
>
> Finally, you cannot open a database on all types of ODBC driver. For
> exemple, while you can to this with SQL-Server, I'm not sure you could
> with JET and many other ISAM databases. Instead of opening a database,
> you should use ODBC Linked Tables.
>
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
>
> "David F." <df2705[ at ]community.nospam> wrote in message
> news:u4$OrIDmHHA.596[ at ]TK2MSFTNGP06.phx.gbl...
>> Hello,
>>
>> I give up try to figure out why I can't have the Access 2003 module open
>> an ODBC connection to a System DSN that is setup and works otherwise.
>>
>> Here's what the help says:
>>
>> "A String that is the name of an existing Microsoft Jet database file, or
>> the data source name (DSN) of an ODBC data source. See the Name property
>> for more information about setting this value."
>>
>> Specifically it says: "or the data source name (DSN) of an ODBC data
>> source"
>>
>> If I add the DSN name to the dbname field then it just says that it Can't
>> find the file. The only way it seems to at least do something is to use
>> "" as the dbname and put "DSN=dnsnamehere" in the connect field. What
>> happens is it pops up the ODBC selection dialog and I have to manually
>> click on System DSN then double click the item in the list.
>>
>> Basically this is where I left it (and yes dnsname gets set to the
>> correct dsn name):
>>
>> Option Compare Database
>>
>> Public Function AddIP(tablename As String, ip As String)
>> Dim db As Database
>> Dim rs As Recordset
>> Dim dsnname As String
>>
>> If ip = "" Then Exit Function
>> dsnname = DLookup("[IPDSN]", "Settings", "[id]=1")
>>
>> Set db = OpenDatabase("", dbDriverNoPrompt, , "ODBC;DSN=" & dsnname)
>>
>>
>>
>
>
>
|
|
On 5/16/07 10:19 PM, in article u4$OrIDmHHA.596[ at ]TK2MSFTNGP06.phx.gbl, "David
F." <df2705[ at ]community.nospam> wrote:
[Quoted Text] > Hello,
>
> I give up try to figure out why I can't have the Access 2003 module open an
> ODBC connection to a System DSN that is setup and works otherwise.
>
> Here's what the help says:
>
> "A String that is the name of an existing Microsoft Jet database file, or
> the data source name (DSN) of an ODBC data source. See the Name property for
> more information about setting this value."
>
> Specifically it says: "or the data source name (DSN) of an ODBC data source"
>
> If I add the DSN name to the dbname field then it just says that it Can't
> find the file. The only way it seems to at least do something is to use ""
> as the dbname and put "DSN=dnsnamehere" in the connect field. What happens
> is it pops up the ODBC selection dialog and I have to manually click on
> System DSN then double click the item in the list.
>
> Basically this is where I left it (and yes dnsname gets set to the correct
> dsn name):
>
> Option Compare Database
>
> Public Function AddIP(tablename As String, ip As String)
> Dim db As Database
> Dim rs As Recordset
> Dim dsnname As String
>
> If ip = "" Then Exit Function
> dsnname = DLookup("[IPDSN]", "Settings", "[id]=1")
>
> Set db = OpenDatabase("", dbDriverNoPrompt, , "ODBC;DSN=" & dsnname)
>
>
>
|
|
|