Basically, I want to 'link' the tables for record modifications, entries, et
c., so that the client (within a WAN) has the GUI front-end that he/she is a
ccustomed to (Access). ODBC seems old and slow, and I wanted to try somethi
ng new - ADO/ADOX/OLEDB? I
s there an easy way to make the connection, iterate through the tables (NOT
the sql system tables) that automatically includes the columns and maintains
that 'link' or connection while the AccessXP GUI front-end is open? This w
ould, of course, need to be
able to support multiple users at the same time.
Right now I get a "Compile error: user defined type not defined" on the fir
st line, but was hoping this was getting close...
THANK YOU!
(On-load event of start-up form.)
Public Function linkTables()
Dim oCat As ADOX.Catalog
Dim oTable As ADOX.Table
Dim sConnString As String
Dim avarSourceTables() As Variant
avarSourceTables = Array("dbo_tblHandouts", _
"dbo_tblParticipants", _
"dbo_tblSiteNetworks", _
"dbo_tblSubEvent", _
"dbo_tblSubReceivables", _
"dbo_xContacts", _
"dbo_xSites", _
"dbo_xTblEventStatus", _
"dbo_xTblNetwork", _
"dbo_xTblPartStatus", _
"dbo_xTblRate", _
"dbo_xTblRateSubType", _
"dbo_xTblReceivables", _
"dbo_xTblRequestors", _
"dbo_xTblRoomStyle", _
"dbo_xTblReports", _
"dbo_xTblSites", _
"dbo_xTblType")
' Set SQL Server connection string used in linked table.
sConnString = "ODBC;" & _
"Driver={SQL Server};" & _
"Server=servernameC;" & _
"Database=databasename;" & _
"Trusted_Connection=Yes;" & _
"Uid=oops;" & _
"Pwd=password;"
' Create a new Table object
For i = LBound(avarSourceTables) To UBound(avarSourceTables)
Set oTable = New ADOX.Table
With oTable
.Name = avarSourceTables(i)
Set .ParentCatalog = oCat
.Properties("Jet OLEDB:Create Link") = True
.Properties("Jet OLEDB:Remote Table Name") = avarSourceTables(i)
.Properties("Jet OLEDB:Link Provider String") = sConnString
End With
' Add Table object to database
oCat.Tables.Append oTable
oCat.Tables.Refresh
Next i
End Function
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...If you choose to use ADO you need to create your recordsets in code and not
rely on linked tables, which are not supported by ADO...
Steve
"Janet" <janetb@.mtn.ncahec.org> wrote in message
news:uTQj5tO4DHA.1644@.TK2MSFTNGP10.phx.gbl...
quote:
> Basically, I want to 'link' the tables for record modifications, entries,
etc., so that the client (within a WAN) has the GUI front-end that he/she is
accustomed to (Access). ODBC seems old and slow, and I wanted to try
something new - ADO/ADOX/OLEDB? Is there an easy way to make the
connection, iterate through the tables (NOT the sql system tables) that
automatically includes the columns and maintains that 'link' or connection
while the AccessXP GUI front-end is open? This would, of course, need to be
able to support multiple users at the same time.
quote:
> Right now I get a "Compile error: user defined type not defined" on the
first line, but was hoping this was getting close...
quote:
> THANK YOU!
>
> (On-load event of start-up form.)
> Public Function linkTables()
> Dim oCat As ADOX.Catalog
> Dim oTable As ADOX.Table
> Dim sConnString As String
> Dim avarSourceTables() As Variant
> avarSourceTables = Array("dbo_tblHandouts", _
> "dbo_tblParticipants", _
> "dbo_tblSiteNetworks", _
> "dbo_tblSubEvent", _
> "dbo_tblSubReceivables", _
> "dbo_xContacts", _
> "dbo_xSites", _
> "dbo_xTblEventStatus", _
> "dbo_xTblNetwork", _
> "dbo_xTblPartStatus", _
> "dbo_xTblRate", _
> "dbo_xTblRateSubType", _
> "dbo_xTblReceivables", _
> "dbo_xTblRequestors", _
> "dbo_xTblRoomStyle", _
> "dbo_xTblReports", _
> "dbo_xTblSites", _
> "dbo_xTblType")
> ' Set SQL Server connection string used in linked table.
> sConnString = "ODBC;" & _
> "Driver={SQL Server};" & _
> "Server=servernameC;" & _
> "Database=databasename;" & _
> "Trusted_Connection=Yes;" & _
> "Uid=oops;" & _
> "Pwd=password;"
> ' Create a new Table object
> For i = LBound(avarSourceTables) To UBound(avarSourceTables)
> Set oTable = New ADOX.Table
> With oTable
> .Name = avarSourceTables(i)
> Set .ParentCatalog = oCat
> .Properties("Jet OLEDB:Create Link") = True
> .Properties("Jet OLEDB:Remote Table Name") = avarSourceTables(i)
> .Properties("Jet OLEDB:Link Provider String") = sConnString
> End With
> ' Add Table object to database
> oCat.Tables.Append oTable
> oCat.Tables.Refresh
> Next i
> End Function
>
> ****************************************
******************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...|||Thanks so much for the straight-forward answer Steve. Poop!-is my response.
But, at least I'm not getting things like "You need to make sure you've se
lected the right library." and I keep trying to tweak and search forever.
Thanks again. I'll go back to the odbc file dsn.
Speaking of which, what I thought I could avoid in the first place, I've got
the mdb on a file server and the sql odbc file dsn in the same folder. Two
people can open the file fine, but one gets an odbc failed error with no co
de or description. All are
running same os, same office version, with same version of sql odbc driver (
2000.81.9042.00)
Any clues?
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...|||"Janet" <janetb@.mtn.ncahec.org> wrote in message
news:u01g9bP4DHA.1816@.TK2MSFTNGP12.phx.gbl...
quote:
> Thanks so much for the straight-forward answer Steve. Poop!-is my
response. But, at least I'm not getting things like "You need to make sure
you've selected the right library." and I keep trying to tweak and search
forever.
quote:
> Thanks again. I'll go back to the odbc file dsn.
> Speaking of which, what I thought I could avoid in the first place, I've
got the mdb on a file server and the sql odbc file dsn in the same folder.
Two people can open the file fine, but one gets an odbc failed error with no
code or description. All are running same os, same office version, with
same version of sql odbc driver (2000.81.9042.00)
quote:
> Any clues?
Back when I was doing Access development, then migrated to Access - SQL
Server integration, I always preferred placing Access on the desktop for a
"true" client/server solution. While it takes more time, work, and testing
to get it right you'll have a far more robust solution.
If you have not discovered this book, I highly recommend getting this:
Microsoft Access Developer's Guide to SQL Server
by Andy Baron & Mary Chipman <SAMS>
ISBN: 0672319446
Steve
