ODBC and Access2000

[lanelouna]

hello all
perhaps you passed through the same problem so i am gonna post it here
with a database access95, or the same base opened with access 2000, i can connect to oracle, and attach tables from (machine data source?) however creating a base in 2000, and then trying to attach tables, that doesn't work, the connection to oracle fails

odbc failed
[orale][odbc oracle driver]I(#0)
do you have a solution for such a problem?
thanks
Lina

 

[RacerGirl117]

We have an accounting package called Real World that we use to talk to Access 2000 databases that requires an ODBC connection. We have software called Pervasive to help translate the data (because the stuff coming from Real World is DOS based and undelimited). Maybe you need something similar? Jessica Morgan
Fire Fighter Sales & Service Co.
Pittsburgh, PA

http://www.firefighter-pgh.com

 

[lanelouna]

i don't think such a translator is necessary
well i don't know

 

[RacerGirl117]

It was just a thought. I'm not sure how Oracle works. Jessica Morgan
Fire Fighter Sales & Service Co.
Pittsburgh, PA

http://www.firefighter-pgh.com

 

[oharab]

Lina,
I connect to our personnel Oracle database using A2k with no problems (apart from the age it takes to open!)

To do this I created a File DSN to the Oracle database in the ODBC manager (Data Sources in the Control Panel) then used that to link create my linked tables.

I actually did it using code as I wanted to link a load of tables. My code is:

Sub LinkTables()
Dim db As DAO.Database
Dim td As DAO.TableDef
Dim thistd As DAO.TableDef
Dim db2 As DAO.Database
Set db2 = CurrentDb


Set db = OpenDatabase("", , True, "ODBC;DRIVER={Microsoft ODBC for Oracle};UID=d3hr;SERVER=hrlive;PWD=password;DATABASE=&quot
For Each td In db.TableDefs
If Left(td.Name, 2) = "HR" Then
Set thistd = db2.CreateTableDef
With thistd
.Connect = db.Connect
.SourceTableName = td.Name
.Name = Replace(td.Name, ".", "*&quot
End With
On Error Resume Next
db2.TableDefs.Append thistd
On Error GoTo 0
End If
Next
End Sub

When you run the code you get the ODBC dialog box open & I just choose the file dsn I created.
Like I said, depending on how big your Oracle db is, this could take a while to run, this took 20 mins or so to link 840 tables.

Jessica,
Pervasive is another type of database. It is based on the old btreive I think. I'm not sure why you are exporting the data into that from Real World, then importing it into Access, but then I don't know anything about your data, but I can get Access to import almost anything!



Cheers

Ben ----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"
NoFX-The Decline
----------------------------------------------

 

[lanelouna]

Hello Ben
thank you for your code
can you explain to me where do i find info about UID?
is it related to SID in tnsnames?
because i don't know much about this
thank you
Lina

 

[oharab]

To be honest, I have no idea!!!
the uid on my code is the user name I log into Oracle with.
I know very little about Oracle, just enough to get by! try looking on

http://www.orafaq.com.

Ben ----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"
NoFX-The Decline
----------------------------------------------

 

[lanelouna]

Ben i am sorry to bother you
if you can give me a last suggestion that would be great
here is it, the code works, if itfinds the table with the name i specify, he steps into the "if", however at the end i don't see any table attached, why is that?
i tried, debug.print td.name to see the name of the tables, and i see the nname of the tables that exist in the database i connect to, like it is supposed to be.
any suggestions? but why it doesn ' t do anything?

thank you
Lina

 

[oharab]

No problems, that's what the forum is for.
For some reason I could not link some tables into Access, it just threw up an ODBC error, so I put in the lines


On Error Resume Next
db2.TableDefs.Append thistd
On Error GoTo 0


so I wouldn't keep getting error messages.
If you comment out
On Error Resume Next
then you will see what the errors are.

Sorry I can't be much more help, you may want to try the Oracle groups when you know the error message.

Ben
----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"
NoFX-The Decline
----------------------------------------------

 

[lanelouna]

alright
thank you very much
Regards
Lina

 

[RacerGirl117]

Ben,

You know, I couldn't tell you why we have the Pervasive/Btrieve link. I think it's only used when we pull data directly from the Real World source. Fortunately, I've avoided getting involved in that part of the process. I don't think I want to know. lol Jessica Morgan
Fire Fighter Sales & Service Co.
Pittsburgh, PA

http://www.firefighter-pgh.com