connection string shortcut

[abgoosht]

Hello everyone,
I have a form. In the vba code behind it i am repeating a connection string in several places. I have two questions
1) what can i do so that i only have one connection string for the whole code?
2) if later i have several forms that use the same connection string in their code, is there a way where i could only have once central location? sort of like an include file....

The reason i ask this is that later on if they choose to change the database information, i'd rather change is on once place rather than several different places.

Thank you,
-s

 

[boblarson]

What are you connecting to?

Anyway, I would maybe store it in a table and then just use a function to do a DLookup on that table when you need it. That way you can change it if necessary without recoding.

 

[pbaldy]

In a standard module:

Public Const strConnectionString As String = "Provider=..."

You can then use that string anywhere in code.

 

[abgoosht]

hi guys,
thank you so much for your fast reply!
i'm actually connecting to an sql server. so my connection string is like this

    Dim Conn As New ADODB.Connection
    Conn.Open "Provider=SQLOLEDB.1;Data Source=SERVER01;Initial Catalog=rpt_dev;User Id=rptDev_user;Password=testpass;"

does this make a difference?

thank you,
-s

 

[pbaldy]

No; if I had copied more of mine, you would have seen it was a SQL Server connection string.

 

[abgoosht]

great! thank you!

Edit: Is there a way that I could set it up to be used in different forms? i think i was vague. i mean, is there a way to place this string in a central location that i could reference it in any form?

thanks again!

 

[boblarson]

great! thank you!

Edit: Is there a way that I could set it up to be used in different forms? i think i was vague. i mean, is there a way to place this string in a central location that i could reference it in any form?

thanks again!

You can reference it in any form by storing it in the table.

Then create a function in a standard module (not form and not report) like:

Function GetConn() As String
   GetConn = Nz(DLookup("FieldNameInTable","TableNameHere"),"")
End Function

Then when you need it you just set -

Dim conn As New ADODB.Connection
Conn.Open GetConn()

 

[pbaldy]

great! thank you!

Edit: Is there a way that I could set it up to be used in different forms? i think i was vague. i mean, is there a way to place this string in a central location that i could reference it in any form?

thanks again!

Again, if you put it in a standard module (not behind a form), you can use it from anywhere.

 

[abgoosht]

You can reference it in any form by storing it in the table.

Then create a function in a standard module (not form and not report) like:

Function GetConn() As String
   GetConn = Nz(DLookup("FieldNameInTable","TableNameHere"),"")
End Function

Then when you need it you just set -

Dim conn As New ADODB.Connection
Conn.Open GetConn()

Thank you so much bob,
I created the table. i placed the string in there. i even used a message box to make sure that I have it in my form. However, once i type

Conn.Open GetConn()

I get the following error on that line

Run-time error '-2147467259(80004005)':
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

any suggestions as to why this error pops up?

thank you,
-s

 

[boblarson]

Thank you so much bob,
I created the table. i placed the string in there. i even used a message box to make sure that I have it in my form. However, once i type

Conn.Open GetConn()

I get the following error on that line

Run-time error '-2147467259(80004005)':
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

any suggestions as to why this error pops up?

thank you,
-s

You might need to try this:
Conn.Open Chr(34) & GetConn() & Chr(34)

But I haven't tested it. I would have thought it was bringing back a string and all it should work. I'll see if I can do some testing here.

 

[abgoosht]

You might need to try this:
Conn.Open Chr(34) & GetConn() & Chr(34)

But I haven't tested it. I would have thought it was bringing back a string and all it should work. I'll see if I can do some testing here.

thanks again for all the help. i tried it and it gave the same error, so i tried these two cases

Conn.Open Str(Chr(34) & GetConn() & Chr(34))

Conn.Open Chr(34) & Str(GetConn()) & Chr(34)

both of the came back with the same error.....
Run-time error '13':
Type mismatch

thanks,
-s

 

[abgoosht]

see this is one of the things that scares me in access. all i did was undo the latest changes that we talked about. so i have

Conn.Open GetConn()

and now it works......!?

 

[boblarson]

Interesting.

 

[abgoosht]

Interesting.

i have no idea why...
nonetheless THANK YOU SO MUCH!