Access XP to SQL 7 Linked Tables

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

Access SQL problems using MS Jet OLEDB with ADO+VB6

Hi there. I am having trouble with querying a record set by date range using the Between operator. Im using VB 6 with ADO.

Referenced: Microsoft ActiveX Data Objects 2.8 Library

Connection String: "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\localdata\orders.mdb;Persist Security Info=False"

SQL Query used: "SELECT * FROM tbl_orders WHERE (InsertDate BETWEEN #12/07/2004# AND #14/07/2004#)"

Code:

QueryString is passed as "(InsertDate BETWEEN #12/07/2004# AND #14/07/2004#)"

Public Function GetOrders(ByRef OrderArray() As OrderDetails, QueryString As String) As Boolean

On Error GoTo errorHandler

'\\ Adds a new customer to the local database.

Dim dbConnection As ADODB.Connection
Dim dbRecordSet As ADODB.Recordset
Dim intLoopIndex As Integer

If Len(Application.LocalDBConnection) < 1 Then

MsgBox "No local database connection parameter provided. Please run the configuration utility. Unable to get orders.", 48, "Warning"

Exit Function

End If

Set dbConnection = New ADODB.Connection
Set dbRecordSet = New ADODB.Recordset

'Connect to the database
dbConnection.ConnectionString = Application.LocalDBConnection
dbConnection.Open

'Build SQL command
With dbRecordSet

.Open "SELECT * FROM tbl_orders WHERE " & QueryString, dbConnection, adOpenStatic, adLockReadOnly

If Not (.RecordCount < 1) Then

ReDim OrderArray(.RecordCount - 1)

intLoopIndex = 0

.MoveFirst

Do

OrderArray(intLoopIndex).Customer = .Fields("Customer").Value
OrderArray(intLoopIndex).CustOrdNo = .Fields("CustOrdNo").Value
OrderArray(intLoopIndex).Description = .Fields("Description").Value
OrderArray(intLoopIndex).User = .Fields("User").Value
OrderArray(intLoopIndex).Total = .Fields("Total").Value
OrderArray(intLoopIndex).InsertDate = .Fields("InsertDate").Value

.MoveNext

intLoopIndex = (intLoopIndex + 1)

Loop Until (.EOF)

GetOrders = True

End If

End With

'Close recordset and connection
If (dbRecordSet.State = adStateOpen) Then

dbRecordSet.Close

End If

If (dbConnection.State = adStateOpen) Then

dbConnection.Close

End If

'Dispose of DB objects
Set dbConnection = Nothing
Set dbRecordSet = Nothing

Exit Function

errorHandler:

'Close recordset and connection
If (dbRecordSet.State = adStateOpen) Then

dbRecordSet.Close

End If

If (dbConnection.State = adStateOpen) Then

dbConnection.Close

End If

'Dispose of DB objects
Set dbConnection = Nothing
Set dbRecordSet = Nothing

ShowError Err, "modFunctions.GetOrders()"

End Function


Problem:

The recordset returns a record that has the field 'InsertDate' equal to 15/07/2004. This is not within my date range specified.

The actual orders.mdb file is attached to this post.

Any help much appreciated!

Regards,
-Matthew Hall.Hi,

The following query will solve your requirement.

SELECT * FROM tbl_orders WHERE (InsertDate BETWEEN #7/12/2004# AND #7/14/2004#)

I believe Access evaluates date values as mm/dd/yyyy by default. If a supplied value is not valid in this format, Access will attempt to convert it. Therefore, the query you supplied would be equal to ... BETWEEN #12/07/2004# AND #7/14/2004#, which would indeed return 7/15/2004.|||Thank you for your response.

Is this not dependent on the systems regional settings?

Is there an option I can set in the actual Access file that will set the date to be in the correct format, or am I forced to use American format dates?

Access odbc linked table -SQL Server

I have users running an Access 97 application to update records in a SQL
Server 2000 database, per an odbc linked table. After modifying a record
from within the Access application, the linked table shows the updated
record in Access; but, sometimes, the change does not propagate to the table
in SQL Server. How can that be? Exactly when does an update commit to the
SQL Server table? Thanx.Hi Vince,
May be IMPLICIT_TRANSACTION is SET in the database.
You can run DBCC USEROPTIONS, to verify the same.
If it is on, you need to understand the implications of turning it off.
--
Thanks
Yogish|||You could set it up using a view and a linked server rather than a
linked table. That should query it real time. So you'd have this
Select * from
OPENQUERY(
LINKEDSERVER,
'SELECT * FROM TBL_NAME')
I think that'd be a good work around
CecilCable
---
Posted via http://www.webservertalk.com
---
View this thread: http://www.webservertalk.com/message922826.html

access multiple sqlserver database using VB

How can i access multiple sqlserver database using VB
eg. insert 1 record in a database from other databse.Create a linked server in the database that your VB app logs into, and use that via a four part name.

-PatP

access link table

Hi,All
I created a view in our SQL server database, which in the view I can delete
a record according to the cascade delete relationship. Now, we would like to
do it in Access database, so I created an ODBC link to our access database,
from access database I tried to delete the record as well, I got the error
message:
ODBC-delete on a linked table 'dbo_vwSpecialConcerts' failed.
[Microsofe][ODBC SQL Server Drive][SQL Server]View or function
'dbo.vwSpecialConcerts' is not updatable because the modification affects
multiple base tables. (#4405).
Any ideas to solve the problem ?
It's hard to say without knowing what's in the view, but I suspect
you'll need to perform the update against the base table(s) or create
a view with no joins.
--Mary
On Mon, 14 Feb 2005 16:45:06 -0800, "Symphony"
<Symphony@.discussions.microsoft.com> wrote:

>Hi,All
>I created a view in our SQL server database, which in the view I can delete
>a record according to the cascade delete relationship. Now, we would like to
>do it in Access database, so I created an ODBC link to our access database,
>from access database I tried to delete the record as well, I got the error
>message:
>ODBC-delete on a linked table 'dbo_vwSpecialConcerts' failed.
>[Microsofe][ODBC SQL Server Drive][SQL Server]View or function
>'dbo.vwSpecialConcerts' is not updatable because the modification affects
>multiple base tables. (#4405).
>Any ideas to solve the problem ?

access link table

Hi,All
I created a view in our SQL server database, which in the view I can delete
a record according to the cascade delete relationship. Now, we would like to
do it in Access database, so I created an ODBC link to our access database,
from access database I tried to delete the record as well, I got the error
message:
ODBC-delete on a linked table 'dbo_vwSpecialConcerts' failed.
[Microsofe][ODBC SQL Server Drive][SQL Server]View or function
'dbo.vwSpecialConcerts' is not updatable because the modification affects
multiple base tables. (#4405).
Any ideas to solve the problem ?It's hard to say without knowing what's in the view, but I suspect
you'll need to perform the update against the base table(s) or create
a view with no joins.
--Mary
On Mon, 14 Feb 2005 16:45:06 -0800, "Symphony"
<Symphony@.discussions.microsoft.com> wrote:

>Hi,All
>I created a view in our SQL server database, which in the view I can delete
>a record according to the cascade delete relationship. Now, we would like t
o
>do it in Access database, so I created an ODBC link to our access database,
>from access database I tried to delete the record as well, I got the error
>message:
>ODBC-delete on a linked table 'dbo_vwSpecialConcerts' failed.
>[Microsofe][ODBC SQL Server Drive][SQL Server]View or function
>'dbo.vwSpecialConcerts' is not updatable because the modification affects
>multiple base tables. (#4405).
>Any ideas to solve the problem ?

Access Front End and SQL Server 2000 Record Level Access Control

Hello,

We are using Access ADP as a front end, SQL Server 2000 as a back end.
We have a customer contact database. We would like to limit certain
users to only receive certain records based on Windows NT group
membership.

For example, Eastern Sales Group can see clients located in their
region, but they cannot see clients located in the Northern Region.

Is there an elegant way to do this? Below a two solutions which have
been proposed, but none seem to fit. Access is required as a Front End
for its ease of use.

*********
** 1 **
*********
Add an additional attribute (bit mask value) to tblCustomers, and
query appropriately based on the user's group membership.

Problem:
Access allows users direct access to the underlying table.

*********
** 2 **
*********
Create a separe table for each group (effectively splitting
tblCustomers into smaller, separate tables based on group access).
Then, apply SQL Server security on the objects to enforce the business
rules.

Problem:
Does this break Normal Form? I've never seen a solution like this.

I've googled and found similar questions, but not a good solution.

Any suggestions would be appreciated.

Thanks,
Jeff
Jeffrey Walton
noloader.at.yahoo.com"Noloader" <noloader@.yahoo.com> wrote in message
news:6b543aa7.0404231447.777fe29c@.posting.google.c om...
> Hello,
> We are using Access ADP as a front end, SQL Server 2000 as a back end.
> We have a customer contact database. We would like to limit certain
> users to only receive certain records based on Windows NT group
> membership.
> For example, Eastern Sales Group can see clients located in their
> region, but they cannot see clients located in the Northern Region.
> Is there an elegant way to do this? Below a two solutions which have
> been proposed, but none seem to fit. Access is required as a Front End
> for its ease of use.
> *********
> ** 1 **
> *********
> Add an additional attribute (bit mask value) to tblCustomers, and
> query appropriately based on the user's group membership.
> Problem:
> Access allows users direct access to the underlying table.
> *********
> ** 2 **
> *********
> Create a separe table for each group (effectively splitting
> tblCustomers into smaller, separate tables based on group access).
> Then, apply SQL Server security on the objects to enforce the business
> rules.
> Problem:
> Does this break Normal Form? I've never seen a solution like this.
> I've googled and found similar questions, but not a good solution.
> Any suggestions would be appreciated.
> Thanks,
> Jeff
> Jeffrey Walton
> noloader.at.yahoo.com

In general, there is no reason why your users should need to access tables
directly. If you manage data access through stored procedures, then the
procedures can check role membership and only return or update those rows
which are permitted to the user. You could also use views, based on role
membership, or use application roles and handle everything in the client
application.

I would avoid solution 2, because it duplicates data - whatever information
you use to partition the data could also be put in a column. But if you have
a large amount of data, and if users only access their own data, then this
might also be an option.

Simon|||RE/
>We have a customer contact database. We would like to limit certain
>users to only receive certain records based on Windows NT group
>membership.

Based on what little experience I've had doing an Access front end against SQL
Server, I'd set SQL Server security so that nobody and nothing can get directly
to the tables in question and then develop stored procedures and/or views to
move the data back-and-forth between tables and app.
--
PeteCresswell

Access equivalent to @@identity

Hi,
I am trying to get the id of the record that was just inserted into the table. Unfortunately, I have to use Microsoft Access and @.@.identity will not work. Is there some other command that performs the same operation? I am hesitant to use MAX().
Your comments are greatly appreciated.
Thanks.Sorry, I didn't see a forum for Microsoft Access. Maybe this topic should be moved to "Other"??|||4GuysFromRolla.com : ASP FAQS : Databases, General
How do I get the record number of a just added record, using an Access database table? by Bill Wilkinson - 11/3/2000
http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=79

ADO FAQ - Q9) How do I get the AutoNumber (or Identity) for a newly inserted record?
http://www.able-consulting.com/ADO_Faq.htm#Q9

INFO: Identity and Auto-Increment Fields in ADO 2.1 and Beyond
http://support.microsoft.com/support/kb/articles/q233/2/99.ASP

INFO: Identity (AutoIncrement) Columns in ADO or RDS
http://support.microsoft.com/support/kb/articles/q195/9/10.asp

User Tips: Getting the ID of the Just Inserted Database Record by Dan H. - 12/26/2000
http://www.4guysfromrolla.com/webtech/tips/t122600-1.shtml