By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,952 Members | 959 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,952 IT Pros & Developers. It's quick & easy.

LinkedTableManager Automation

P: n/a
RLN
RE: Access 2003
I have an Access app that uses SQl Server as the back end.
I am trying to automate a few processes using Linked Table Manager.

I would like to do 4 things via VBA:
1. Connect to a SQL Server DB via VBA (without an ODBC connection) when
the app starts up.
2. (In a separate form via command button) Start up the Linked Table
Manager.
3. Parse through the tables linked and check them to see if they are
still valid; if the current table is bad and does not exist in the
database, remove that bad link & write that one to a log file and check
the next one.
4. Parse through my local table of valid SQL Server tablenames, and
re-link these specific tables.
Connecting to my SQL Server DB works fine via ODBC, but would like to
do away with ODBC headaches.
For item 1: Here is my code so far which I cannot get to connect, due
to an "invalid property" error:
<begin code>
Dim oConn As Connection
Set oConn = New ADODB.Connection
oConn.Connect "Driver={SQL Server};" & _
"Server=MySQLServer;" & _
"Database=MyDBName;" & _
"Uid=MyUid;" & _
"Pwd=MyPwd"
MsgBox "oConn connect string is: " & oConn.ConnectionString
<end code>

Item #2 - starting up the Linked Table Mgr
I tried this but this raises an "invalid procedure call or argument"
error:

CommandBars("MenuBar").Controls("Tools").Controls( "Add-Ins").Controls("Linked
Table Manager ").Execute ""

For items 3 & 4, I'm simply wanting to drop linked table and re-link
new ones via VBA.

I'm also looking for additional resources for setting up an ADO
connection to SQL Server after a DSN-less connection is made via VBA,
but it is tricky(maybe because I've never tried it before)

Thanks in advance (for what might appear to be elementary questions.)

Oct 27 '06 #1
Share this Question
Share on Google+
7 Replies


P: n/a
Your post raises many questions.

An ADP may give you what you want. I don't use bound ADPs anymore for
any serious work, but I think their OK in a safe, friendly environment.

ADO connections generally use the ConnectionString property.
Initializing each required property individually seems to be uncommon
because properties vary across providers and the names of many of the
properties are not single words. With MS-SQL Server I have success as
follows:

Dim c As ADODB.Connection
Set c = New ADODB.Connection
With c
.CursorLocation = adUseClient Or adUseServer
' no not the bitwise or ... use one or the other
.Provider = "sqloledb.1"
With .Properties
.Item("Data Source") = "ServerName/Address"
.Item("Initial Catalog") = "DataBaseName"
.Item("PassWord") = "Password"
.Item("User ID") = "UserId"
End With
.Open
Debug.Print (.State And adStateOpen) = adStateOpen
End With
End Sub

Oct 28 '06 #2

P: n/a
their = they're
argghh

Oct 28 '06 #3

P: n/a
"Lyle Fairfield" wrote
their = they're
argghh
Isn't it amazing how, as we mature, fingers seem to develop a mind of their
own?

Larry
Oct 30 '06 #4

P: n/a
RLN
Lyle:

This code returns true on the Debug statement and works nicely, thank
you.
Once connected, can you tell me how I could list all of the tables
available in a specific database and link to them via VBA? I was
searching several section in MSDN but did not find anything on this.

Thanks.

Oct 30 '06 #5

P: n/a
"RLN" <rl***************@yahoo.comwrote in
news:11**********************@i42g2000cwa.googlegr oups.com:
Lyle:

This code returns true on the Debug statement and works nicely, thank
you.
Once connected, can you tell me how I could list all of the tables
available in a specific database and link to them via VBA? I was
searching several section in MSDN but did not find anything on this.

Thanks.
I tried to reply on Google Groups a minute ago but got an OOPS error. No,
not OOP, Oops!
Here we go again:

This lists all the tables in a db:

Dim c As ADODB.Connection
Set c = New ADODB.Connection
Dim r As ADODB.Recordset
With c
.CursorLocation = adUseServer
' no not the bitwise or ... use one or the other
.Provider = "sqloledb.1"
With .Properties
.item("Data Source") = "ServerName/Address"
.item("Initial Catalog") = "DataBaseName"
.item("PassWord") = "Password"
.item("User ID") = "UserId"
End With
.Open
Debug.Print (.State And adStateOpen) = adStateOpen
Set r = .OpenSchema(adSchemaTables, Array(Empty, Empty, Empty,
"TABLE"))
With r
While Not .EOF
Debug.Print .Fields("TABLE_NAME").Value
.MoveNext
Wend
End With
End With

"Link to them in VBA"? Hmmm ... I'm not sure what you want to do.

An ADP establishes a connection to an MS-SQL db and one might say the
tables are "linked". ADP connections can be made/modified in VBA but this
is not so common.

I think most Access devlopers use ODBC to link to MS-SQL tables. I don't;
an answer from someone who does is needed here; it's likely to be better
than anything I could contribute.

--
Lyle Fairfield

from http://msdn.microsoft.com/library/de...l=/library/en-
us/dnmdac/html/data_mdacroadmap.asp

Obsolete Data Access Technologies
Obsolete technologies are technologies that have not been enhanced or
updated in several product releases and that will be excluded from future
product releases. Do not use these technologies when you write new
applications. When you modify existing applications that are written
using these technologies, consider migrating those applications to
ADO.NET.
The following components are considered obsolete:
....
Data Access Objects (DAO): DAO provides access to JET (Access) databases.
This API can be used from Microsoft Visual Basic®, Microsoft Visual C++®,
and scripting languages. It was included with Microsoft Office 2000 and
Office XP. DAO 3.6 is the final version of this technology. It will not
be available on the 64-bit Windows operating system.
.....
Oct 30 '06 #6

P: n/a
Lyle Fairfield wrote:
I think most Access devlopers use ODBC to link to MS-SQL tables. I don't;
an answer from someone who does is needed here; it's likely to be better
than anything I could contribute.
Well, upon further review I decided I would like to add this to my
repertoire, so bororwed and slightly changed the connect procedure from
Danny Lesandrini and cam up with this.

Sub dub0()
Dim c As ADODB.Connection
Dim r As ADODB.Recordset
Dim t As DAO.TableDef
Set c = New ADODB.Connection

With c
.CursorLocation = adUseServer
' no not the bitwise or ... use one or the other
.Provider = "sqloledb.1"
With .Properties
.item("Data Source") = "__________"
.item("Initial Catalog") = "__________"
.item("PassWord") = "__________"
.item("User ID") = "__________"
End With
.Open
Debug.Print (.State And adStateOpen) = adStateOpen
Set r = .OpenSchema(adSchemaTables, Array(Empty, Empty, Empty,
"TABLE"))
With r
While Not .EOF
DSNLessLinkTable .Fields("TABLE_NAME").Value
.MoveNext
Wend
DBEngine(0)(0).TableDefs.Refresh
End With
End With
End Sub

Public Sub DSNLessLinkTable(ByVal TableName As String)
' by Danny Lesandrini
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strConnect As String

strConnect = "ODBC;DRIVER={SQL Server}" _
& ";SERVER=__________" _
& ";DATABASE=__________" _
& ";UID=__________" _
& ";PWD=__________"

Set db = CurrentDb()
Set tdf = db.CreateTableDef(TableName)
tdf.SourceTableName = TableName

tdf.Connect = strConnect

db.TableDefs.Append tdf
db.TableDefs.Refresh

Set tdf = Nothing
Set db = Nothing
End Sub

It ain't pretty but it works (here!). If I were to do this for real
it's likely I would morph the two procedures into one and use variables
for the various strings that must be sent off and get the thing down to
a fewer lines.

Oct 30 '06 #7

P: n/a
Lyle:

Sorry for the delayed response...lots going on out here at work.

I tried this code and it worked, only when I have an OBDC data source
set up for the database I am trying to connect to via VBA code.

I copied my application to a 2nd workstation that I know does not have
an ODBC data source set up (but I recognizes the SQL Server in my code
via SQL Server Mgmt Express)
and it does not connect.

Is your code here somehow (or is Access somehow) dependent under the
rug on requiring an ODBC data source to be set up on each workstation?

I'm simply trying to connect to SQL Server via code only, maiking the
application more portable.
Any other suggestions you might have would be helpful at this point.

Thank you.

Nov 8 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.