ODBC connection thru VBA code using ADOX 1

[cmmrfrds]

I am trying to adopt some Microsoft code to create an odbc connection to an sql server table. This works on my home set up using 'localhost' as the server, but not at work. The server is set up for both types of authentications. My goal is to eliminate setting up odbc manually on all the clients pc's.

Code. '- shows a fake password

Public Function SQLServerLinkedTable()
Dim oCat As ADOX.Catalog
Dim oTable As ADOX.Table
Dim sConnString As String

' Set SQL Server connection string used in linked table.
sConnString = "ODBC;" & _
"Driver={SQL Server};" & _
"Server=(Boxer);" & _
"Database=ReportInventory;" & _
"Uid=rptinvapps;" & _
"Pwd=xxxxx;"

' Create and open an ADOX connection to Access database
Set oCat = New ADOX.Catalog
oCat.ActiveConnection = CurrentProject.Connection

' Create a new Table object
Set oTable = New ADOX.Table

With oTable
.Name = "dbo_tblReportRequestPrelim"
Set .ParentCatalog = oCat
.Properties("Jet OLEDB:Create Link") = True
.Properties("Jet OLEDB:Remote Table Name") = "dbo.tblReportRequestPrelim"
.Properties("Jet OLEDB:Link Provider String") = sConnString
End With

' Add Table object to database
oCat.Tables.Append oTable
oCat.Tables.Refresh
Set oCat = Nothing

End Function

Error.
odbc --connection to server boxer failed.
Note. my work password contains an asterisk. Could this be a problem?

 

[xuereveds]

Just straying away from the ADOX a little and looking more at the manual ODBC creation could you not just write a simple REG file that the db calls on opening and creates the ODBC for you and you can then use conventional ADO?

Just a Thought.

 

[NorthNone]

I don't have your answer, but do know where you might find a clue or two. There is a section on Microsoft's website "Migrating from DAO to ADO" that is useful just to learn ADO (you don't have to know DAO at all).
Here's the URL:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndao/html/daotoadoupdate.asp

*Then select: Defining and Retrieving a Database's Schema
**Under that select: Creating and Modifying Tables
---Here's the code they listed.----------
Sub ADOCreateAttachedODBCTable()
'ADOX with the Microsoft Jet Provider has a separate property that specifies the connection
'string for tables attached through ODBC. When creating tables attached through ODBC you may
'want to indicate that the password should be saved as part of the connection string'
'(it is not saved by default). With ADOX, use the Jet OLEDB:Cache Link Name/Password property
'to indicate that the password should be cached. This is equivalent to setting the
'dbAttachSavePWD flag in the Table object's Attributes property using DAO.
Dim cat As New ADOX.Catalog
Dim tbl As New ADOX.Table
' Open the catalog
cat.ActiveConnection = CurrentProject.Connection
' Set the name and target catalog for the table
tbl.Name = "Profile_fund"
Set tbl.ParentCatalog = cat
' Set the properties to create the link
tbl.Properties("Jet OLEDB:Create Link") = True
tbl.Properties("Jet OLEDB:Link Provider String") = _
"ODBC;DSN=SponProj;UID=;PWD=;"
tbl.Properties("Jet OLEDB:Remote Table Name") = "dbo.Tran_income"
' Append the table to the collection
cat.Tables.Append tbl
Set cat = Nothing
End Sub
I hope this helps a bit, but know it is not the concise answer you were hoping for. I don't see major differences between theirs and yours, but there may be something subtle.

---------------------------------------
The customer may not always be right, but the customer is always the customer.

 

[FancyPrairie]

I use a data link file. All you have to do is place the file in a location that everyone has access to. To create a data link file (filetype .UDL), Simply create a text file (with nothing in it) (use NotePad or whatever). Then change the file type from .txt to .UDL. Then double click the file to open it. The Data Link Properties window will then be opened which will allow you to define the connection.

In your code, do the following:

Dim cnn As ADODB.Connection

Set cnn = New ADODB.Connection
cnn.Provider = "SQLOLEDB.1"
cnn.Open "File Name=\\pathWhereUDL Resides\YourDataLinkFile.UDL"

 

[cmmrfrds]

I used the example provided at this location. I can get it to work on my home setup where I have control over sql server. So, I think it is security related, but the logon and password will work when I manually set up an odbc connection. Also, if I use a DSN that is on my pc it will work, but this is what I wanted to avoid. Here is the link that I used as an example.

http://www.able-consulting.com/ADOX_Faq.htm#Q4

 

[FancyPrairie]

You setup either a User DSN or System DSN. You should have setup a File DSN. Which works the same was as a UDL. You can either use a File DSN for your connection or a UDL. One of the books I have doesn't mention the File DSN method but mentions the UDL. So I've been using the UDL. Try it. It works, basically, like setting up a User or System DSN.

 

[cmmrfrds]

Thank you for the information FancyPrairie. What I am trying to do is have an sql server linked table in an Acccess MDB file. I can get it to link using a DSN which I set up on my PC, but for the clients I was trying to make it transparent and NOT need to set up the odbc on each PC. I am using Access 2000 mdb and a linked table is easy to use as the record source on Forms and Reports.

 

[FancyPrairie]

Right, you stated that before, but I got focused on the UDL. Sorry. However, you can do what you want by linking to a File DSN rather than a User DSN or System DSN. You create a File DSN the same way you create a User DSN or System DSN (Start | Control Panel | Adimistrative Tools | Data Sources (ODBC)). You will see the File DSN tab, in addition to the User and System tabs. Simply select the File DSN tab and answer the questions. Then, when you want to link to a SQL table, use the File DSN rather than the way you have been doing it. This way, you don't have to set up ODBC connections on each of your users' machines. Just make sure the File DSN file is located on a server that everyone has access to. I would set up a share name rather than using a drive letter to access the File DSN (i.e. \\shareName\file.dsn vs g:\file.dsn)

 

[cmmrfrds]

Okay that makes sense, I will give that a try tomorrow. I will use the unc naming convention.

 

[cmmrfrds]

I got it to work after making some adjustments to the connection string. The only down side I see now is that since it is using the ADOX library, if the client has an older version of the library the reference is broken initially and has to be reset. Thank you FancyPrairie for your suggestion, that gives me 2 options at this point.

Here is my final code that works.
sConnString = "ODBC;" & _
"Driver={SQL Server};" & _
"Server={Boxer};" & _
"Database=ReportInventory;" & _
"Trusted_Connection=No;" & _
"Uid=rptinvapps;" & _
"Pwd=xxxxx;"

' Create and open an ADOX connection to Access database
Set oCat = New ADOX.Catalog
oCat.ActiveConnection = CurrentProject.Connection

' Create a new Table object
Set oTable = New ADOX.Table

With oTable
.Name = "dbo_tblReportRequestPrelim"
Set .ParentCatalog = oCat
.Properties("Jet OLEDB:Create Link") = True
.Properties("Jet OLEDB:Remote Table Name") = "dbo.tblReportRequestPrelim"
.Properties("Jet OLEDB:Cache Link Name/Password") = True
.Properties("Jet OLEDB:Link Provider String") = sConnString
End With