How To Open or Load ODBC Connection?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, Can someone tell me which method, property, function to use to program my
form to use a defined ODBC connection?

I have an Access file (.mdb) and in this file I have about 8 tables linked
via and ODBC connection. I have about 10 queries setup to pull info/data from
those linked tables and then I have about 6 reports which are formatted to
display the data in those queries. Lastly, I have one form which I am using
as a print center for those reports. Basically I have command buttons setup
so that when they are clicked it brings up the report print preview, page
setup and print function so that the report can be printed.

Now, my connection works fine on my computer because I have a local ODBC
connection source setup, but I want to load this databse file onto our local
network server and when I do that, when another machine tries to run the form
and print reports from it I get an error which says the ODBC connection
failed. Now I'm assuming it's because there is no local ODBC connection setup
on all the other machines.

Can someone tell which how to load the connection through Access instead of
using a local ODBC connection?? I've looked at many different methods,
properties, functions, etc and am really confused as to which one to use and
how and when to deploy it. Any help is GREATLY appreciated!
 
What are you linking to? And did you use mapped drive or UNC path in the DSN
that was used to create the links? Once you create the links the frontend no
longer uses the DSN. The path is stored in a hidden table 'MSysObjects' in
the 'Connect' column. You'll want to use a UNC path that all users would
have access to create the DSN and then relink the tables.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Hi, Can someone tell me which method, property, function to use to program
my
| form to use a defined ODBC connection?
|
| I have an Access file (.mdb) and in this file I have about 8 tables linked
| via and ODBC connection. I have about 10 queries setup to pull info/data
from
| those linked tables and then I have about 6 reports which are formatted to
| display the data in those queries. Lastly, I have one form which I am
using
| as a print center for those reports. Basically I have command buttons
setup
| so that when they are clicked it brings up the report print preview, page
| setup and print function so that the report can be printed.
|
| Now, my connection works fine on my computer because I have a local ODBC
| connection source setup, but I want to load this databse file onto our
local
| network server and when I do that, when another machine tries to run the
form
| and print reports from it I get an error which says the ODBC connection
| failed. Now I'm assuming it's because there is no local ODBC connection
setup
| on all the other machines.
|
| Can someone tell which how to load the connection through Access instead
of
| using a local ODBC connection?? I've looked at many different methods,
| properties, functions, etc and am really confused as to which one to use
and
| how and when to deploy it. Any help is GREATLY appreciated!
 
Now one has responded yet so how about this:
If I have a VBA module which has the following settings described in it
under General Declarations, how can I call upon it to be used?

Public const (which describes sServerIP)
Public const (which describes sDSN)
Global const (which has a string defining the connection)
 
Dave - Thanks for your reply.
The tables are linked through a Machine Data Source which is specified as
"System" type and has a description of "Pervasive ODBC Client Interface".
When I choose the data source name it asks me for name and password which I
put in, then it gives me a list of all available tables, then I choose which
table to link to and I click the box which says to save password. My linked
tables are then in my "Tables" window pane.
 
I've never used that interface but it seems you may need to load the ODBC
components for the Pervasive PSQL engine on each of the clients.

http://www.pervasive.com/library/index.asp?_portal=ODBC

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Dave - Thanks for your reply.
| The tables are linked through a Machine Data Source which is specified as
| "System" type and has a description of "Pervasive ODBC Client Interface".
| When I choose the data source name it asks me for name and password which
I
| put in, then it gives me a list of all available tables, then I choose
which
| table to link to and I click the box which says to save password. My
linked
| tables are then in my "Tables" window pane.
 
As far as I know I shouldn't have to setup individual client data sources.
That's what I'm trying to avoid. But I'm not exactly sure. If I define Public
or Global const is there a way to call on those as some type of Sub or
Function in VBA? because I have these setup under General/Declarations but
I'm not sure how to call them into use:
Public const (which describes sServerIP)
Public const (which describes sDSN)
Global const (which has a string defining the connection)
 
Can you post the string in the 'Connect' column of the hidden table
'MSysObjects'

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| As far as I know I shouldn't have to setup individual client data sources.
| That's what I'm trying to avoid. But I'm not exactly sure. If I define
Public
| or Global const is there a way to call on those as some type of Sub or
| Function in VBA? because I have these setup under General/Declarations but
| I'm not sure how to call them into use:
| Public const (which describes sServerIP)
| Public const (which describes sDSN)
| Global const (which has a string defining the connection)
 
I look in the Help menu to find out how to unhide MSysObjects Table and I
found it but it says I can only open it as Read Only. In the table is a
"Connect" field with "Memo" as its data type but it is blank. The only
settings listed for "Connect" are as follows:
Required: No
Allow Zero Length: Yes
Indexed: No
Unicode Compression: No
IME Mode: No Control
IME Sentence Mode: None
No Lookup value listed either.
There's also a field name called "Database" which is setup exactly the same
as "Connect".
Now if this file would need to be changed I'm not sure how I would do that
if I can only open as Read Only?
 
No, not design view. No don't change a thing. Just open the table to view
the records. The [Connect] column will be populated in the rows related to
your linked tables with the current connection string. Please post this.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
|I look in the Help menu to find out how to unhide MSysObjects Table and I
| found it but it says I can only open it as Read Only. In the table is a
| "Connect" field with "Memo" as its data type but it is blank. The only
| settings listed for "Connect" are as follows:
| Required: No
| Allow Zero Length: Yes
| Indexed: No
| Unicode Compression: No
| IME Mode: No Control
| IME Sentence Mode: None
| No Lookup value listed either.
| There's also a field name called "Database" which is setup exactly the
same
| as "Connect".
| Now if this file would need to be changed I'm not sure how I would do that
| if I can only open as Read Only?
|
|
| "Dave Patrick" wrote:
|
| > Can you post the string in the 'Connect' column of the hidden table
| > 'MSysObjects'
| >
| > --
| >
| > Regards,
| >
| > Dave Patrick ....Please no email replies - reply in newsgroup.
| > Microsoft Certified Professional
| > Microsoft MVP [Windows]
| > http://www.microsoft.com/protect
| >
| > "la knight" wrote:
| > | As far as I know I shouldn't have to setup individual client data
sources.
| > | That's what I'm trying to avoid. But I'm not exactly sure. If I define
| > Public
| > | or Global const is there a way to call on those as some type of Sub or
| > | Function in VBA? because I have these setup under General/Declarations
but
| > | I'm not sure how to call them into use:
| > | Public const (which describes sServerIP)
| > | Public const (which describes sDSN)
| > | Global const (which has a string defining the connection)
| >
| >
| >
 
Back
Top