Always linking to the first project

[jcopeland38053]

MSAccess 2003 / XP / Oracle 9.2 backend
Before I get started explaining it will be helpful to see my code snipet:
-------------------------------------------
tablename = 1

On Error Resume Next

For tablename = 1 To 2
If tablename = 1 Then pdtable = "pdtable_10 1"
If tablename = 2 Then pdtable = "pdtable_11 1"
DoCmd.DeleteObj ect acTable, pdtable
Next tablename

cboSite.SetFocu s
strSite = cboSite.Text
txtUser.SetFocu s
strUser = txtUser.Text
txtPass.SetFocu s
strPass = txtPass.Text

tablename1 = 1

odbcstring = "ODBC;Driver={M icrosoft ODBC for Oracle};Server= " + strSite + ";UID=" + strUser + ";PWD=" + strPass + ""

On Error Resume Next

For tablename1 = 1 To 2
If tablename1 = 1 Then pdtable = "pdtable_10 1"
If tablename1 = 2 Then pdtable = "pdtable_11 1"
DoCmd.TransferD atabase acLink, "ODBC Database", odbcstring, _
acTable, pdtable, pdtable, 0, True
Next tablename1
------------------------------
(there are actually 11 tables that i am linking, i just cut it down for space)
I have 2 Projects:
Project1 & 2 Server: MYSERVER
Project1 UID = Ted
Project1 Pass = Williams
Project2 UID = John
Project2 Pass = Jones
-------------------------------
Problem: When I start the app for Project1 and there are no tables initailly linked, everything works fine. Then I switch to project 2 by supplying the UID and Pass and click on attach tables (which runs this code). The existing tables are deleted and the correct UID, Pass for Project2 are used. However, it links Project1 tables again. Any ideas?

[jaxjagfan]

MSAccess 2003 / XP / Oracle 9.2 backend
Before I get started explaining it will be helpful to see my code snipet:
-------------------------------------------
tablename = 1

On Error Resume Next

For tablename = 1 To 2
If tablename = 1 Then pdtable = "pdtable_10 1"
If tablename = 2 Then pdtable = "pdtable_11 1"
DoCmd.DeleteObj ect acTable, pdtable
Next tablename

cboSite.SetFocu s
strSite = cboSite.Text
txtUser.SetFocu s
strUser = txtUser.Text
txtPass.SetFocu s
strPass = txtPass.Text

tablename1 = 1

odbcstring = "ODBC;Driver={M icrosoft ODBC for Oracle};Server= " + strSite + ";UID=" + strUser + ";PWD=" + strPass + ""

On Error Resume Next

For tablename1 = 1 To 2
If tablename1 = 1 Then pdtable = "pdtable_10 1"
If tablename1 = 2 Then pdtable = "pdtable_11 1"
DoCmd.TransferD atabase acLink, "ODBC Database", odbcstring, _
acTable, pdtable, pdtable, 0, True
Next tablename1
------------------------------
(there are actually 11 tables that i am linking, i just cut it down for space)
I have 2 Projects:
Project1 & 2 Server: MYSERVER
Project1 UID = Ted
Project1 Pass = Williams
Project2 UID = John
Project2 Pass = Jones
-------------------------------
Problem: When I start the app for Project1 and there are no tables initailly linked, everything works fine. Then I switch to project 2 by supplying the UID and Pass and click on attach tables (which runs this code). The existing tables are deleted and the correct UID, Pass for Project2 are used. However, it links Project1 tables again. Any ideas?

How does it know which "Project" it is running? Are you using Site for this? You will need to change the "odbcstring " depending on the project running. Are the table names the same?

Dim iProj as int
iProj = Me.txtProject
Select Case iProj
Case 1
...set the project1 variables
Case 2
...set the project2 variables
End Select

odbcstring = "ODBC;Driver={M icrosoft ODBC for Oracle};Server= " + strSite + ";UID=" + strUser + ";PWD=" + strPass + ""

If your table naming structure is as stated above you can make the following change to minimize code:

For tablename1 = 1 To 11
pdtable = "pdtable_1" & Format(tablenam e1,"000")
DoCmd.DeleteObj ect acTable, pdtable
DoCmd.TransferD atabase acLink, "ODBC Database", odbcstring, _
acTable, pdtable, pdtable, 0, True
Next tablename1

[jcopeland38053]

How does it know which "Project" it is running? Are you using Site for this? You will need to change the "odbcstring " depending on the project running. Are the table names the same?

Dim iProj as int
iProj = Me.txtProject
Select Case iProj
Case 1
...set the project1 variables
Case 2
...set the project2 variables
End Select

odbcstring = "ODBC;Driver={M icrosoft ODBC for Oracle};Server= " + strSite + ";UID=" + strUser + ";PWD=" + strPass + ""

If your table naming structure is as stated above you can make the following change to minimize code:

For tablename1 = 1 To 11
pdtable = "pdtable_1" & Format(tablenam e1,"000")
DoCmd.DeleteObj ect acTable, pdtable
DoCmd.TransferD atabase acLink, "ODBC Database", odbcstring, _
acTable, pdtable, pdtable, 0, True
Next tablename1

The variable strSite, strUser and strPass are from a dialog box. The username sets which set of tables are used. The terminology here is a "schema." So from my example Project1, which is the Ted schmea, it sees its own set of pdtables. Then when I log into the John schema it only sees Project2's set of pdtbales. So, when I step throught thecade as it runs, the ODBC string is correct (i.e. it is using Project2's UID and Pass). Its amost as if the first set of tables are in residual memory and, because the names are the same, it just reataches the original tables. And yes the tables names are the same.

[jaxjagfan]

The variable strSite, strUser and strPass are from a dialog box. The username sets which set of tables are used. The terminology here is a "schema." So from my example Project1, which is the Ted schmea, it sees its own set of pdtables. Then when I log into the John schema it only sees Project2's set of pdtbales. So, when I step throught thecade as it runs, the ODBC string is correct (i.e. it is using Project2's UID and Pass). Its amost as if the first set of tables are in residual memory and, because the names are the same, it just reataches the original tables. And yes the tables names are the same.

I notice in your connection string you are not specifying an instance or a particular database on the server. I assume "strSite" is pointing to a different server for each Project. If "strSite" is always pointing to the same server and no instance or database is specified, it will always default to the same instance or database (master in the case of SQL Server) and you will always get the same tables with your code or mine.

This is a connection string for SQL Server:
"Driver={SQLSer ver};Server=You r_Server_Name;D atabase=Your_Da tabase_Name;Uid =Your_Username; Pwd=Your_Passwo rd;"
It may be slightly different for Oracle.

FYI - You don't need to set focus on a textbox or combo to get the value in it

Function doProject()
 
Dim strSite as String, strUser as String, strPass as String, tablename as int
strSite = Me.cboSite
strUser = Me.txtUser
strPass = Me.txtPass
 
odbcstring = "ODBC;Driver={Microsoft ODBC for Oracle};Server=" &  strSite & ";UID=" & strUser & ";PWD=" & strPass  & "
 
On Error Resume Next
 
tablename = 1
 
For tablename1 = 1 To 11
pdtable = "pdtable_1" & Format(tablename1,"000")
DoCmd.DeleteObject acTable, pdtable
DoCmd.TransferDatabase acLink, "ODBC Database", odbcstring, _
acTable, pdtable, pdtable, 0, True
Next tablename1
 
End Function
 

[jcopeland38053]

I notice in your connection string you are not specifying an instance or a particular database on the server. I assume "strSite" is pointing to a different server for each Project. If "strSite" is always pointing to the same server and no instance or database is specified, it will always default to the same instance or database (master in the case of SQL Server) and you will always get the same tables with your code or mine.

This is a connection string for SQL Server:
"Driver={SQLSer ver};Server=You r_Server_Name;D atabase=Your_Da tabase_Name;Uid =Your_Username; Pwd=Your_Passwo rd;"
It may be slightly different for Oracle.

FYI - You don't need to set focus on a textbox or combo to get the value in it

Function doProject()
 
Dim strSite as String, strUser as String, strPass as String, tablename as int
strSite = Me.cboSite
strUser = Me.txtUser
strPass = Me.txtPass
 
odbcstring = "ODBC;Driver={Microsoft ODBC for Oracle};Server=" &  strSite & ";UID=" & strUser & ";PWD=" & strPass  & "
 
On Error Resume Next
 
tablename = 1
 
For tablename1 = 1 To 11
pdtable = "pdtable_1" & Format(tablename1,"000")
DoCmd.DeleteObject acTable, pdtable
DoCmd.TransferDatabase acLink, "ODBC Database", odbcstring, _
acTable, pdtable, pdtable, 0, True
Next tablename1
 
End Function
 

Thanks for the tip on not needing the setfocus. That will clean up code throughout my program.
Also, as for the reduced FOr loop, unfortunately my table numbers are sparatic (101,111,112,13 5, etc.) just happened to only include the first two tables. But thanks for that info, too.
As for the connection string, in Oracle the Server variable is the SID of the DB. In that SID I create users. Since we are going a little deeper I will tell you the actual user name is of the format pd_<project#> (e.g. pd_9500, pd_6500). So the user PD_9500 has the pdtables assgined to it. Then the user pd_6500 has its own pdtables assigned to it. The names of the pdtables are the same (e.g. pdtable101, etc.) but since they are assigned to the individual users, if I login to the DB as pd_9500, I only quer its pdtbales and the same goes for pd_6500.
So, in short, when I connect using strUser and strPass, I should be linking their respective tables, but its not.

Thanks for all the help so far.

[PianoMan64]

Wouldn't it just be simpler to do all this in VBA and make the connection to your Oracle backend simply by specificing the accual user, then you wouldn't have the overhead of linking each table to the application, you just simply connect via OLEDB to your Oracle source?

If you're wanting code examples, let me know

Thanks,

Joe P.

[jaxjagfan]

Thanks for the tip on not needing the setfocus. That will clean up code throughout my program.
Also, as for the reduced FOr loop, unfortunately my table numbers are sparatic (101,111,112,13 5, etc.) just happened to only include the first two tables. But thanks for that info, too.
As for the connection string, in Oracle the Server variable is the SID of the DB. In that SID I create users. Since we are going a little deeper I will tell you the actual user name is of the format pd_<project#> (e.g. pd_9500, pd_6500). So the user PD_9500 has the pdtables assgined to it. Then the user pd_6500 has its own pdtables assigned to it. The names of the pdtables are the same (e.g. pdtable101, etc.) but since they are assigned to the individual users, if I login to the DB as pd_9500, I only quer its pdtbales and the same goes for pd_6500.
So, in short, when I connect using strUser and strPass, I should be linking their respective tables, but its not.

Thanks for all the help so far.

Is pd_6500 and pd_9500 logging into the same SID? A SID can have multiple databases (instances) and most likely does. I know you said each "pd" is assigned it own tables but check the "pd" defaults in Oracle security to ensure that each "pd" is not defaulting to the same database. You may have to include the database or instance name in your odbc connection string to make this work.

[jcopeland38053]

Wouldn't it just be simpler to do all this in VBA and make the connection to your Oracle backend simply by specificing the accual user, then you wouldn't have the overhead of linking each table to the application, you just simply connect via OLEDB to your Oracle source?

If you're wanting code examples, let me know

Thanks,

Joe P.

I am generating reports from the DB and it requires some pretty ugly joins and queries that I know how to create in access.

[jcopeland38053]

Is pd_6500 and pd_9500 logging into the same SID? A SID can have multiple databases (instances) and most likely does. I know you said each "pd" is assigned it own tables but check the "pd" defaults in Oracle security to ensure that each "pd" is not defaulting to the same database. You may have to include the database or instance name in your odbc connection string to make this work.

When I connect manually using a system DSN to pd_6500 the tables in the selection box appear as pd_6500.pdtable _101, etc. I tired coding this and I still get the same result. There is actaully a 3rd party CAD software that is writing to the DB so the realtionships beween tables and users have to be correct.

From the sounds of thing I may have to just use one AccessDB per project, which isnt a bad thing, just would have been nice to be able to use the one AccessDB/VBA for all projects.

[jaxjagfan]

When I connect manually using a system DSN to pd_6500 the tables in the selection box appear as pd_6500.pdtable _101, etc. I tired coding this and I still get the same result. There is actaully a 3rd party CAD software that is writing to the DB so the realtionships beween tables and users have to be correct.

From the sounds of thing I may have to just use one AccessDB per project, which isnt a bad thing, just would have been nice to be able to use the one AccessDB/VBA for all projects.

Normally this "pd_6500.pdtabl e_101" would indicate "database.table ".
Is pd_6500 the name of the databases as well?

Try adding the Datebase to the odbc string to ensure it's pointing to correct database.

If user name and database name are the same then use "strUser"

Dim strPD as string

strPD = Me.MyControlFor PDSelection

odbcstring = "ODBC;Driver={M icrosoft ODBC for Oracle};Server= " & strSite & ";UID=" & strUser & ";PWD=" & strPass & ";DATABASE= " & strPD & "