Hard Code Data Source

[ewong1]

I am building a database that is going to have multiple users and would like to hard code my datasources into the database. The reason behind this is because the multiple datasources are required in my ODBC Datasource admin on each computer. Is there a way for me to hard code the connection to the datasource into my database? Thanks for your help!

 

[Robert88]

Not sure what type of tables you are using but if it is Access, might be better to use the database splitter so that you have a front end and back end (containing all your data).This will split the data away from the rest of the development which means you can locate it on a folder shared on a network for multiple users.

Then using the "Linked Table Manager", reconnect the front end (located locally) to the back end (located on the server)?

If it is not Access table you will need to do diffrent, let me know if this helps if this is what you are trying to do?

Robert88

 

[Robert88]

Connecting to Jet Databases...........

Here is a sample of coding the connection if it is an access table

SubOpenMyDB()

Dim cnn1 As New ADODB.connection
Dim rst1 As ADODB.Recordset

'Create the connection
cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb;"

'Create recordset reference, and set its properties
Set rst1 = New ADODB.Recordset
rst1.CursorType = adOpenKeyset
rst1.LockType = adLockOptimistic

'Open recordset, and print a test record
rst1.Open "Customers", cnn1
Debug.Print rst1.Fields(0).Value, rst1.Fields(1).Value

'Clean up objects
rst1.Close
cnn1.Close
Set rst1 = Nothing
Set cnn1 = Nothing

End Sub

Taken straight out of "Programming Microsoft Office Access 2003", Rick Dobson.

Hope it helps.

Robert88

 

[ewong1]

Thanks for getting back to me. I actually need to set up the connection through an oracle database on another server. Is it still possible in the manner you provided?

 

[Robert88]

Connecting Access to Oracle table

Only a sample

Option Compare Database
Option Explicit

Function AttachTable() As Variant
  On Error GoTo AttachTable_Err

  Dim db As Database
  Dim tdef As TableDef
  Dim strConnect As String

  Set db = CurrentDb()
  strConnect = "ODBC;DSN=oraweb;DBQ=qit-uq-cbiw_oraweb;DATABASE="
  ' NOTE: DSN is your ODBC Data Source Name; DBQ is your TNSNAMES.ORA entry name

  Set tdef = db.CreateTableDef("MY_ACCESS_TABLENAME")
  ' tdef.Attributes DB_ATTACHEDODBC
  tdef.Connect = strConnect
  tdef.SourceTableName = "MY_ORACLE_TABLENAME"
  db.TableDefs.Append tdef
	
AttachTable_Exit:
  Exit Function
	
AttachTable_Err:
  MsgBox "Error: " & Str(Err) & " - " & Error$ & " occured in global module."
  Resume AttachTable_Exit
	
End Function

taken from:
http://www.orafaq.com/faq/how_does_one_attach_an_oracle_table_in_ms_access

good luck with it, hope it helps.

Robert88

 

[ewong1]

WOW I'm a programming newb... I need some help understanding a bit more. If I hard code a datasource table like mentioned in the code, how do I go about creating queries and such off of the table? Does it show up in access like a table that can be queried or do I Have to run all of my queries in programming and build reports off of them through code?

 

[Robert88]

Hi ewong1,

I suppose like any linked table, it appears in your table tab as a table. I have never done it with Oracle but I have done it with Access Jet databases. I cannot see why Oracle would be different.

So like any table within your database, queries can be built on it. In addition, forms and reports based on your queries, usually the order in which you create information based on tables. Within Access of course.

Hope this helps.

Robert88