Create Linked tables in access db without ODBC

T

Terence C

I would like to retrieve data from external database (e.g. SQL server,
Oracle, .., etc) and put them into linked tables in access db (access 2000).
But I don't want to set up DSN in each computer. So it is possible to do it
programtically without ODBC? Could anyone advise? Thanks a lot.
 
S

strive4peace

Hi Terence,

You can write pass-through queries in the native language for SQL Server
-- these will be queries and not tables, but you can, of course use them
as you would use a linked table.

To see how the pass-through query is constructed, do this:

link to one of the tables
write a query for it
convert the query to a pass-through -->
Query, SQL Specific, Pass-Through

look at the SQL and pattern after that for the other tables

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
:) have an awesome day :)
*
 
S

Stefan Hoffmann

hi Terence,

Terence said:
I would like to retrieve data from external database (e.g. SQL server,
Oracle, .., etc) and put them into linked tables in access db (access 2000).
But I don't want to set up DSN in each computer. So it is possible to do it
programtically without ODBC? Could anyone advise? Thanks a lot.
You want DSN-less connections:

http://www.accessmvp.com/djsteele/DSNLessLinks.html


mfG
--> stefan <--
 
S

strive4peace

oh, cool! I was trying to remember this one -- now I made a note of it,
thanks Stefan -- coming to the Summit?

Warm Regards,
Crystal

*
:) have an awesome day :)
*
 
S

Stefan Hoffmann

hi Crystal,
oh, cool! I was trying to remember this one -- now I made a note of it,
thanks Stefan -- coming to the Summit?
I haven't made a final decision yet, but I'm quite sure I will.


mfG
--> stefan <--
 
T

Terence C

Hi Stefan,

Thanks for your help! It works fine for SQL server. But here comes another
problem, I have to create a linked table in Access 2003 and the data is
retrieved from AS400, but it gets the error message "Cannot find installable
ISAM"..... there is no problem with getting data from SQL server using the
same method, however, doesn't work with AS400..... any suggestion?? Thanks a
lot!!

Terence
 
R

Rick Brandt

Hi Stefan,

Thanks for your help! It works fine for SQL server. But here comes
another problem, I have to create a linked table in Access 2003 and the
data is retrieved from AS400, but it gets the error message "Cannot find
installable ISAM"..... there is no problem with getting data from SQL
server using the same method, however, doesn't work with AS400..... any
suggestion?? Thanks a lot!!

Do you have an ODBC driver for the AS400 installed? FWIW I have never
gotten a DSNless connection to work with an AS400. I just create the DSN
when the app starts.
 
T

Terence C

Hi Rick,

Yes, the ODBC driver is installed on the computer.......

You mean you create the DNS dynamically when the app starts?? Could you give
me more information on that? Thanks.

Terence
 
S

Stefan Hoffmann

hi Terence,

Terence said:
Thanks for your help! It works fine for SQL server. But here comes another
problem, I have to create a linked table in Access 2003 and the data is
retrieved from AS400, but it gets the error message "Cannot find installable
ISAM"..... there is no problem with getting data from SQL server using the
same method, however, doesn't work with AS400..... any suggestion?? Thanks a
lot!!
See

http://www.connectionstrings.com/as-400

for the necessary settings.

But Rick is right, some ODBC drivers cannot be used. I had once the
problem with Oracle. It was not possible to setup a working DSN-less
connection for the customers environment.


mfG
--> stefan <--
 
T

Terence C

Hi Stefan,

I have already used the connection string mentioned in the web site below
but still doesn't work.....

http://www.connectionstrings.com/as-400

I also tried to reinstall MS Office again but still can't get it. I think
you are right, there is no way to create a DNS-less connection with some 3rd
party databases at this stage........

So you finally creatdeda DNS entry in ODBC for the connection with Oracle??
 
S

Stefan Hoffmann

hi Terence,

Terence said:
I have already used the connection string mentioned in the web site below
but still doesn't work.....
They depend on the driver installed, maybe you can upgrade your drivers?
So you finally creatdeda DNS entry in ODBC for the connection with Oracle??
Yes, I deployed a User DSN. But you may try the code in

http://www.eggheadcafe.com/forumarchives/VisualBasicdatabase/Sep2005/post23796700.asp

for creating a DSN with VBA.


mfG
--> stefan <--
 
T

Terence C

Hi Stefan,

Thanks for your information.

In the link that you provided, the example:

Public Function CreateDSN()

Dim sConnect as string

Dim sDriver as string

sDriver = "Client Access ODBC Driver (32 bit)"

sConnect = "Description=MyConnection" & vbNullChar & _

"System=MySystem" & vbNullChar & _

"DBQ=MyDBQ"

DBEngine.Registerdatabase "MyDSN", sDriver, True, sConnect

End Function

Have you ever followed this example to create a DSN with AS400
programatically?

What are the values of "MyConnection" and "MyDBQ" in the connection string
sConnect ??
Could you advise? Thanks a lot.

Ivan
 
S

Stefan Hoffmann

hi Terence,

Terence said:
Have you ever followed this example to create a DSN with AS400
programatically?
Not for an AS400, but in the early days for SQL Server 6.5.
What are the values of "MyConnection" and "MyDBQ" in the connection string
sConnect ??
"MyConnection" is just a plain description text. MyDBQ should be
database identifier.

Create a working file DSN. Then you can extract the necessary parameters
from it with notepad.

mfG
--> stefan <--
 
T

Terence C

Hey guys! Finally I got it!! Actually DSN-less connection can be established
with AS400, the trick is on the connection string:

ODBC;Driver={Client Access ODBC Driver
(32-bit)};System=XXXXXXX;Uid=XXXX;Pwd=XXXXXX;SSL=0;DBQ=XXXXXX;MGDSN=0;

That's it!! Hope this helps!!!!!!
 
R

Rick Brandt

Hey guys! Finally I got it!! Actually DSN-less connection can be
established with AS400, the trick is on the connection string:

ODBC;Driver={Client Access ODBC Driver
(32-bit)};System=XXXXXXX;Uid=XXXX;Pwd=XXXXXX;SSL=0;DBQ=XXXXXX;MGDSN=0;

That's it!! Hope this helps!!!!!!

Doesn't help me I'm afraid. The biggest issue I had with DSNless against
the AS400 was the System value. It doesn't like it if I don't supply a
value and every value that I have ever tried produces an error.

Certainly the value that is described as the "system" on our IBM box is
not accepted. I've also tried the IP, the network name, and a few
others. All produce errors.
 
T

Terence C

Hi Rick,

Your case is quite strange..... For me, I just use the IP address, it works
fine....
 
I

Ivan Grozney

A year late and ...

I had the same issue as Rick but was able to create a linked server on my
SQL Server and then linked my access front end to it and it seems to work
just fine, slow but fine.

Vanya
 
H

Harold Thomas

Terence, this worked for me as well.

I'd tried nearly everything. What is the MSGDSN? That's what did it for me.



Terence wrote:

Hey guys! Finally I got it!!
29-Jan-09

Hey guys! Finally I got it!! Actually DSN-less connection can be established
with AS400, the trick is on the connection string:

ODBC;Driver={Client Access ODBC Driver
(32-bit)};System=XXXXXXX;Uid=XXXX;Pwd=XXXXXX;SSL=0;DBQ=XXXXXX;MGDSN=0;

That's it!! Hope this helps!!!!!!

:

Previous Posts In This Thread:

Create Linked tables in access db without ODBC
I would like to retrieve data from external database (e.g. SQL server,
Oracle, .., etc) and put them into linked tables in access db (access 2000).
But I don't want to set up DSN in each computer. So it is possible to do it
programtically without ODBC? Could anyone advise? Thanks a lot.

Hi Terence,You can write pass-through queries in the native language for SQL
Hi Terence,

You can write pass-through queries in the native language for SQL Server
-- these will be queries and not tables, but you can, of course use them
as you would use a linked table.

To see how the pass-through query is constructed, do this:

link to one of the tables
write a query for it
convert the query to a pass-through -->
Query, SQL Specific, Pass-Through

look at the SQL and pattern after that for the other tables

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
:) have an awesome day :)
*




Terence C wrote:

Re: Create Linked tables in access db without ODBC
hi Terence,

Terence C wrote:
You want DSN-less connections:

http://www.accessmvp.com/djsteele/DSNLessLinks.html


mfG
--> stefan <--

oh, cool!
oh, cool! I was trying to remember this one -- now I made a note of it,
thanks Stefan -- coming to the Summit?

Warm Regards,
Crystal

*
:) have an awesome day :)
*




Stefan Hoffmann wrote:

Re: Create Linked tables in access db without ODBC
hi Crystal,

strive4peace wrote:
I have not made a final decision yet, but I am quite sure I will.


mfG
--> stefan <--

Re: Create Linked tables in access db without ODBC
hope to see you there, Stefan

Warm Regards,
Crystal


*
:) have an awesome day :)
*

Hi Stefan,Thanks for your help!
Hi Stefan,

Thanks for your help! It works fine for SQL server. But here comes another
problem, I have to create a linked table in Access 2003 and the data is
retrieved from AS400, but it gets the error message "Cannot find installable
ISAM"..... there is no problem with getting data from SQL server using the
same method, however, doesn't work with AS400..... any suggestion?? Thanks a
lot!!

Terence

:

Re: Create Linked tables in access db without ODBC
On Sun, 18 Jan 2009 18:48:00 -0800, Terence C wrote:


Do you have an ODBC driver for the AS400 installed? FWIW I have never
gotten a DSNless connection to work with an AS400. I just create the DSN
when the app starts.


--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Hi Rick,Yes, the ODBC driver is installed on the computer.......
Hi Rick,

Yes, the ODBC driver is installed on the computer.......

You mean you create the DNS dynamically when the app starts?? Could you give
me more information on that? Thanks.

Terence

:

Re: Create Linked tables in access db without ODBC
hi Terence,

Terence C wrote:
See

http://www.connectionstrings.com/as-400

for the necessary settings.

But Rick is right, some ODBC drivers cannot be used. I had once the
problem with Oracle. It was not possible to setup a working DSN-less
connection for the customers environment.


mfG
--> stefan <--

Hi Stefan,I have already used the connection string mentioned in the web site
Hi Stefan,

I have already used the connection string mentioned in the web site below
but still doesn't work.....

http://www.connectionstrings.com/as-400

I also tried to reinstall MS Office again but still can't get it. I think
you are right, there is no way to create a DNS-less connection with some 3rd
party databases at this stage........

So you finally creatdeda DNS entry in ODBC for the connection with Oracle??

:

Re: Create Linked tables in access db without ODBC
hi Terence,

Terence C wrote:
They depend on the driver installed, maybe you can upgrade your drivers?

Yes, I deployed a User DSN. But you may try the code in

http://www.eggheadcafe.com/forumarchives/VisualBasicdatabase/Sep2005/post23796700.asp

for creating a DSN with VBA.


mfG
--> stefan <--

Hi Stefan,Thanks for your information.
Hi Stefan,

Thanks for your information.

In the link that you provided, the example:

Public Function CreateDSN()

Dim sConnect as string

Dim sDriver as string

sDriver = "Client Access ODBC Driver (32 bit)"

sConnect = "Description=MyConnection" & vbNullChar & _

"System=MySystem" & vbNullChar & _

"DBQ=MyDBQ"

DBEngine.Registerdatabase "MyDSN", sDriver, True, sConnect

End Function

Have you ever followed this example to create a DSN with AS400
programatically?

What are the values of "MyConnection" and "MyDBQ" in the connection string
sConnect ??
Could you advise? Thanks a lot.

Ivan

:

Re: Create Linked tables in access db without ODBC
hi Terence,

Terence C wrote:
Not for an AS400, but in the early days for SQL Server 6.5.

"MyConnection" is just a plain description text. MyDBQ should be
database identifier.

Create a working file DSN. Then you can extract the necessary parameters
from it with notepad.

mfG
--> stefan <--

Re: Create Linked tables in access db without ODBC
On Fri, 23 Jan 2009 00:02:01 -0800, Terence C wrote:

I don't if this is the "best" way to do it, but when I added code to
create my DSNs programmatically at startup what I did was use code I
found on the web for writing to the registry.

I created an example DSN on my own machine, examined the registry entries
that this produced, then wrote a routine that creates all of those same
entries at application startup. It has always worked just fine doing it
this way and I feel I have more control over all of the various
parameters.

This also addresses your concern of "what values are needed for the
various settings". With my method I am setting values that I have no
idea what they do. I only know that they match what the working DSN that
I created via the Control Panel applet has set.


--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Hey guys! Finally I got it!!
Hey guys! Finally I got it!! Actually DSN-less connection can be established
with AS400, the trick is on the connection string:

ODBC;Driver={Client Access ODBC Driver
(32-bit)};System=XXXXXXX;Uid=XXXX;Pwd=XXXXXX;SSL=0;DBQ=XXXXXX;MGDSN=0;

That's it!! Hope this helps!!!!!!

:

Re: Create Linked tables in access db without ODBC
On Thu, 29 Jan 2009 00:48:02 -0800, Terence C wrote:


Doesn't help me I'm afraid. The biggest issue I had with DSNless against
the AS400 was the System value. It doesn't like it if I don't supply a
value and every value that I have ever tried produces an error.

Certainly the value that is described as the "system" on our IBM box is
not accepted. I've also tried the IP, the network name, and a few
others. All produce errors.


--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Hi Rick,Your case is quite strange.....
Hi Rick,

Your case is quite strange..... For me, I just use the IP address, it works
fine....

:

A year late and ...
A year late and ...

I had the same issue as Rick but was able to create a linked server on my
SQL Server and then linked my access front end to it and it seems to work
just fine, slow but fine.

Vanya

:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Build a Cross-Browser ASP.NET CSS-Only Menu UserControl
http://www.eggheadcafe.com/tutorial...ce-d17d1b482676/build-a-crossbrowser-asp.aspx
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top