f



Access 2000 Linked to SQL Server 2000, openRecordset error

I'm trying to access data in vba using the openrecordset command.  The
data in on a SQL Server 2000 database, and I have linked tables to
that data.  the Table I'm trying to access is one of these linked
tables, and my codes is as follows:

Set vRS = CurrentDb.OpenRecordset(tbl_DataCommentLog)

when this line is executed I get the error:

"Run-time error '3622':

You must use the dbSeeChanges option  with OpenRecordset when
accessing a SQL Server table that has an Identity column. "

so I changed my code to:

Set vRS = CurrentDb.OpenRecordset(vSQL, , dbseeChanges)

When this is executed the following error is generated:

" Run-time error '13':

Type mismatch "

I noticed that it didn't seem to recognise the 'dbseechanges'
arguement as usual the case is correct for known expressions, yet the
's' was not captialised on the 'dbseeChanges' !!!!

Please help this is driving me potty...
0
10/20/2003 11:50:51 AM
comp.databases.ms-access 42670 articles. 0 followers. Post Follow

5 Replies
789 Views

Similar Articles

[PageSpeed] 37

Try

Set vRS = CurrentDb.OpenRecordset(vSQL, dbOpenDynaset, dbseeChanges)

Assuming that vSQL is a valid SQL statement.

Terry

"Philippa" <philippa.speirs@systemc.com> wrote in message
news:9cf39c04.0310200350.7e629291@posting.google.com...
> I'm trying to access data in vba using the openrecordset command.  The
> data in on a SQL Server 2000 database, and I have linked tables to
> that data.  the Table I'm trying to access is one of these linked
> tables, and my codes is as follows:
>
> Set vRS = CurrentDb.OpenRecordset(tbl_DataCommentLog)
>
> when this line is executed I get the error:
>
> "Run-time error '3622':
>
> You must use the dbSeeChanges option  with OpenRecordset when
> accessing a SQL Server table that has an Identity column. "
>
> so I changed my code to:
>
> Set vRS = CurrentDb.OpenRecordset(vSQL, , dbseeChanges)
>
> When this is executed the following error is generated:
>
> " Run-time error '13':
>
> Type mismatch "
>
> I noticed that it didn't seem to recognise the 'dbseechanges'
> arguement as usual the case is correct for known expressions, yet the
> 's' was not captialised on the 'dbseeChanges' !!!!
>
> Please help this is driving me potty...


0
terry.kreft (988)
10/20/2003 12:49:42 PM
Thanks for you responce, I tried what you suggested (yes vsql is a
valid SQL Statement shown below), and it still doesn't work, this time
I get the following error:

" Run-tiem error '3001':

Invalid Argument. "

vSQL = "SELECT * FROM tbl_DataCommentLog WHERE Comment_FileUID = '" &
pForm!Header_FileUID & "'"
pForm being a paramter passed into the sub using the ME in the forms
private sub event. Even when I change vSQL to be just a valid table
name e.g. "tbl_DataCommentLog" it returns the same error !

I have succesfull managed to use the openrecordset method but this was
when the table to a acces table not a linked one.  I think the fact
the vb is not recognising the arguements 'dbOpenDynaset' and
'dbSeeChanges' ( I know this as usually the case of keywords is
corrected, and it wasn't for these words ), is relevant but I don't
understand why !!!!

My tables are linked via a SQL Server driver ODBC connection, is this
significant....?

Cheers Philippa

"Terry Kreft" <terry.kreft@mps.co.uk> wrote in message news:<bn0ll6$9d0
$1@newsreaderg1.core.theplanet.net>...
> Try
> 
> Set vRS = CurrentDb.OpenRecordset(vSQL, dbOpenDynaset, dbseeChanges)
> 
> Assuming that vSQL is a valid SQL statement.
> 
> Terry
> 
> "Philippa" <philippa.speirs@systemc.com> wrote in message
> news:9cf39c04.0310200350.7e629291@posting.google.com...
> > I'm trying to access data in vba using the openrecordset command.  The
> > data in on a SQL Server 2000 database, and I have linked tables to
> > that data.  the Table I'm trying to access is one of these linked
> > tables, and my codes is as follows:
> >
> > Set vRS = CurrentDb.OpenRecordset(tbl_DataCommentLog)
> >
> > when this line is executed I get the error:
> >
> > "Run-time error '3622':
> >
> > You must use the dbSeeChanges option  with OpenRecordset when
> > accessing a SQL Server table that has an Identity column. "
> >
> > so I changed my code to:
> >
> > Set vRS = CurrentDb.OpenRecordset(vSQL, , dbseeChanges)
> >
> > When this is executed the following error is generated:
> >
> > " Run-time error '13':
> >
> > Type mismatch "
> >
> > I noticed that it didn't seem to recognise the 'dbseechanges'
> > arguement as usual the case is correct for known expressions, yet the
> > 's' was not captialised on the 'dbseeChanges' !!!!
> >
> > Please help this is driving me potty...
0
10/21/2003 11:08:43 AM
How is vRS declared?


Terry

"Philippa" <philippa.speirs@systemc.com> wrote in message
news:9cf39c04.0310210308.ae7366d@posting.google.com...
> Thanks for you responce, I tried what you suggested (yes vsql is a
> valid SQL Statement shown below), and it still doesn't work, this time
> I get the following error:
>
> " Run-tiem error '3001':
>
> Invalid Argument. "
>
> vSQL = "SELECT * FROM tbl_DataCommentLog WHERE Comment_FileUID = '" &
> pForm!Header_FileUID & "'"
> pForm being a paramter passed into the sub using the ME in the forms
> private sub event. Even when I change vSQL to be just a valid table
> name e.g. "tbl_DataCommentLog" it returns the same error !
>
> I have succesfull managed to use the openrecordset method but this was
> when the table to a acces table not a linked one.  I think the fact
> the vb is not recognising the arguements 'dbOpenDynaset' and
> 'dbSeeChanges' ( I know this as usually the case of keywords is
> corrected, and it wasn't for these words ), is relevant but I don't
> understand why !!!!
>
> My tables are linked via a SQL Server driver ODBC connection, is this
> significant....?
>
> Cheers Philippa
>
> "Terry Kreft" <terry.kreft@mps.co.uk> wrote in message news:<bn0ll6$9d0
> $1@newsreaderg1.core.theplanet.net>...
> > Try
> >
> > Set vRS = CurrentDb.OpenRecordset(vSQL, dbOpenDynaset, dbseeChanges)
> >
> > Assuming that vSQL is a valid SQL statement.
> >
> > Terry
> >
> > "Philippa" <philippa.speirs@systemc.com> wrote in message
> > news:9cf39c04.0310200350.7e629291@posting.google.com...
> > > I'm trying to access data in vba using the openrecordset command.  The
> > > data in on a SQL Server 2000 database, and I have linked tables to
> > > that data.  the Table I'm trying to access is one of these linked
> > > tables, and my codes is as follows:
> > >
> > > Set vRS = CurrentDb.OpenRecordset(tbl_DataCommentLog)
> > >
> > > when this line is executed I get the error:
> > >
> > > "Run-time error '3622':
> > >
> > > You must use the dbSeeChanges option  with OpenRecordset when
> > > accessing a SQL Server table that has an Identity column. "
> > >
> > > so I changed my code to:
> > >
> > > Set vRS = CurrentDb.OpenRecordset(vSQL, , dbseeChanges)
> > >
> > > When this is executed the following error is generated:
> > >
> > > " Run-time error '13':
> > >
> > > Type mismatch "
> > >
> > > I noticed that it didn't seem to recognise the 'dbseechanges'
> > > arguement as usual the case is correct for known expressions, yet the
> > > 's' was not captialised on the 'dbseeChanges' !!!!
> > >
> > > Please help this is driving me potty...


0
terry.kreft (988)
10/21/2003 3:43:35 PM
Dim vRS As Recordset

"Terry Kreft" <terry.kreft@mps.co.uk> wrote in message news:<bn3k77$br6$1@newsreaderg1.core.theplanet.net>...
> How is vRS declared?
> 
> 
> Terry
> 
> "Philippa" <philippa.speirs@systemc.com> wrote in message
> news:9cf39c04.0310210308.ae7366d@posting.google.com...
> > Thanks for you responce, I tried what you suggested (yes vsql is a
> > valid SQL Statement shown below), and it still doesn't work, this time
> > I get the following error:
> >
> > " Run-tiem error '3001':
> >
> > Invalid Argument. "
> >
> > vSQL = "SELECT * FROM tbl_DataCommentLog WHERE Comment_FileUID = '" &
> > pForm!Header_FileUID & "'"
> > pForm being a paramter passed into the sub using the ME in the forms
> > private sub event. Even when I change vSQL to be just a valid table
> > name e.g. "tbl_DataCommentLog" it returns the same error !
> >
> > I have succesfull managed to use the openrecordset method but this was
> > when the table to a acces table not a linked one.  I think the fact
> > the vb is not recognising the arguements 'dbOpenDynaset' and
> > 'dbSeeChanges' ( I know this as usually the case of keywords is
> > corrected, and it wasn't for these words ), is relevant but I don't
> > understand why !!!!
> >
> > My tables are linked via a SQL Server driver ODBC connection, is this
> > significant....?
> >
> > Cheers Philippa
> >
> > "Terry Kreft" <terry.kreft@mps.co.uk> wrote in message news:<bn0ll6$9d0
> > $1@newsreaderg1.core.theplanet.net>...
> > > Try
> > >
> > > Set vRS = CurrentDb.OpenRecordset(vSQL, dbOpenDynaset, dbseeChanges)
> > >
> > > Assuming that vSQL is a valid SQL statement.
> > >
> > > Terry
> > >
> > > "Philippa" <philippa.speirs@systemc.com> wrote in message
> > > news:9cf39c04.0310200350.7e629291@posting.google.com...
> > > > I'm trying to access data in vba using the openrecordset command.  The
> > > > data in on a SQL Server 2000 database, and I have linked tables to
> > > > that data.  the Table I'm trying to access is one of these linked
> > > > tables, and my codes is as follows:
> > > >
> > > > Set vRS = CurrentDb.OpenRecordset(tbl_DataCommentLog)
> > > >
> > > > when this line is executed I get the error:
> > > >
> > > > "Run-time error '3622':
> > > >
> > > > You must use the dbSeeChanges option  with OpenRecordset when
> > > > accessing a SQL Server table that has an Identity column. "
> > > >
> > > > so I changed my code to:
> > > >
> > > > Set vRS = CurrentDb.OpenRecordset(vSQL, , dbseeChanges)
> > > >
> > > > When this is executed the following error is generated:
> > > >
> > > > " Run-time error '13':
> > > >
> > > > Type mismatch "
> > > >
> > > > I noticed that it didn't seem to recognise the 'dbseechanges'
> > > > arguement as usual the case is correct for known expressions, yet the
> > > > 's' was not captialised on the 'dbseeChanges' !!!!
> > > >
> > > > Please help this is driving me potty...
0
10/22/2003 8:52:14 AM
Not to worry, while looking in my object browser I noticed that there
was no DAO library, I made a new database and imported all my tables
and stuff, and its all working fine now.  Cheers for peoples input.

philippa.speirs@systemc.com (Philippa) wrote in message news:<9cf39c04.0310200350.7e629291@posting.google.com>...
> I'm trying to access data in vba using the openrecordset command.  The
> data in on a SQL Server 2000 database, and I have linked tables to
> that data.  the Table I'm trying to access is one of these linked
> tables, and my codes is as follows:
> 
> Set vRS = CurrentDb.OpenRecordset(tbl_DataCommentLog)
> 
> when this line is executed I get the error:
> 
> "Run-time error '3622':
> 
> You must use the dbSeeChanges option  with OpenRecordset when
> accessing a SQL Server table that has an Identity column. "
> 
> so I changed my code to:
> 
> Set vRS = CurrentDb.OpenRecordset(vSQL, , dbseeChanges)
> 
> When this is executed the following error is generated:
> 
> " Run-time error '13':
> 
> Type mismatch "
> 
> I noticed that it didn't seem to recognise the 'dbseechanges'
> arguement as usual the case is correct for known expressions, yet the
> 's' was not captialised on the 'dbseeChanges' !!!!
> 
> Please help this is driving me potty...
0
10/23/2003 8:46:01 AM
Reply:

Similar Artilces:

Sql Server 2000 linked server to Access 97/2000 incompatability
We are experiencing a problem with Sql Server 2000 linking to an Access 97 file. We have two machines that link to this .mdb file, and we recently upgraded one to newer hardware, SP3a, MDAC 2.8, etc. The link on this upgraded machine no longer works, giving this message: Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. [OLE/DB provider returned message: Cannot open a database created with a previous version of your application.] OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize r...

MicroSoft SQL Server 2000, Service Pack 2, AppDev SQL Server 2000 Accelerated Training [3 CDs], TransTrainer for Designing Microsoft SQL Server 2000 Databases v1.1, SQL Server 2000 Reporting Ser
MicroSoft SQL Server 2000, Service Pack 2, AppDev SQL Server 2000 Accelerated Training [3 CDs], TransTrainer for Designing Microsoft SQL Server 2000 Databases v1.1, SQL Server 2000 Reporting Services, (Enterprise), Developer Edition 64 Bit, other 16,000 more CDs, [ no dongles, no activations, etc ... needed ! ] (free donge)! No time limitation! CD NR 4203 MicroSoft SQL Server 2000 4in1 CD NR 9452 Microsoft SQL Server 2000 Service Pack 2 CD NR 11 298 AppDev SQL Server 2000 Accelerated Training [3 CDs] CD NR 12 000 Microsoft SQL Server 2000 Developer Edition 64 Bit CD NR 13 424 TransTrainer for Designing Microsoft SQL Server 2000 Databases v1.1 CD NR 14 688 SQL Server 2000 Reporting Services (c) Microsoft CD NR 15 412 SQL Server 2000 Reporting Services Enterprise (c) Microsoft please send e-mail, to ola2@mail.gr, zorba@mail.gr ...

Converting SQL Server 2000 query to MS Access 2000 query
Hello! I am trying to convert a query written for SQL Server 2000 database tables, to a MS Access query. Here is what I have in SQL Server: SELECT t2.*, CASE WHEN t2.QType = '3' THEN t1.Note ELSE CASE WHEN t2.QType = '2' THEN CASE WHEN CONVERT(varchar(100), t1.ANumber) = '1' THEN 'Yes' ELSE 'No' END ELSE CASE WHEN CONVERT(varchar(5), t2.Qnumber)+'.' + CONVERT(varchar(5), t1.ANumber) = t2.SubQ THEN 'Selected' ELSE 'Not Selected' END END END AS Client_Response FROM (SELECT * FROM ClientAnswer WHERE (OrganizationID = '491') AND (InstrumentId = 'CM005')) AS t1 RIGHT JOIN (SELECT Questions.InstrumentId, Questions.Qnumber, Questions.Question, Questions.QType, CASE WHEN TEMPT.A IS NULL THEN QNumber ELSE TEMPT.A END AS SubQ, CASE WHEN TEMPT.Answer IS NULL THEN Questions.Question ELSE TEMPT.Answer END AS Answer FROM Questions LEFT OUTER JOIN (SELECT QNumber AS Q, Answer, CONVERT(varchar(5), QNumber) + '.' + CONVERT(varchar(5), ANumber) AS A FROM Answers WHERE InstrumentId = 'CM005')) TEMPT ON Questions.Qnumber = TEMPT.Q WHERE (Questions.InstrumentId = 'CM005')) AS t2 ON t1.QNumber = CASE WHEN t2.QType <> '1' THEN t2.Subq ELSE t2.QNumber END ORDER BY subq It runs perfectly on the SQL Server, howev...

Too few parameters expected 2 error occurs after migrating "MS Access 2000" to "MS SQL Server"
Hello all I get Too few parameters expected 2 error and "The MS Jet Database engine cannot find the input table or query "myTempTablename". Make sure it exists and that its name is spelled correctly. 3078" after I migrated "MS Access 2000" to "MS SQL Server 2000" and relinked all linked tables. The front-end interface of my application is MS Access 2000. The back-end is MS SQL Server 2000. After I migrated the database and relinked all linked tables, "Too few parameters expected 2" and "input table or query "myTempTablename" 3078" error occurs. I found a article in Microsoft website. The "Too few parameter" error occurs because the limitation of the MS Access ODBC driver. If it is, any solution? Thanks in advance Cheers Bon Hi Bon, If you mean you are getting the error when you are trying to relink the tables through ODBC, then the problem could be - if your table(s) had a memo field, that sql server picked up the memo field as a varchar, and Access is trying to read a text field that exceeds 255 chars. You need to go to Query Analyzer (sql server QA) and run some queries against the offending table to see what the text length is in your varchar fields in the sql server table. And if Sql Server did pick up an Access memo field as a Sql Server Text field, ODBC can't read Sql Server Text fields. If any of the varchar fields exceeds 250 chars (for me I get errors after exceeding 250 cha...

Table Linking Access 2000 FE / Access 2000 BE
Can anyone tell me what goes on beneath the hood with table linking in Access? We have a speed issue with our database. It uses access for the front end and the back end. Prior to splitting the database it took 15 seconds to open it over the network. Now with the split it takes 2 minutes 15 seconds to open. We are exploring all options. Advice Please! -Tim Tony Toews has some suggestions at http://www.granite.ab.ca/access/performancefaq.htm and http://www.granite.ab.ca/access/splitapp/index.htm -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Timothy Golden" <tttpppggg@yahoo.com> wrote in message news:5c7f8ade.0408311210.155f97b5@posting.google.com... > Can anyone tell me what goes on beneath the hood with table linking in > Access? > > We have a speed issue with our database. It uses access for the front > end and the back end. Prior to splitting the database it took 15 > seconds to open it over the network. Now with the split it takes 2 > minutes 15 seconds to open. We are exploring all options. Advice > Please! > > -Tim The wire from the network server to your screen is much longer than the wire from your harddrive to the screen. It's just like the Olymics - it takes longer to run the Marathon than it take for the 100M hurdles. Check your network to be sure there's not a defrocked Irish priest trying to push those electrons off the network wire. -- ...

linked server from sql 7 to ms access 2000 ... some tables not available
Hi, I have a linked server in sql 7 that connects to a ms access 2000 database. It has been working for a while, however lately, after we imported a large amount of new data, the database fails to query a certain (large number of records) table. I can query and get responses for every other table except the largest table. select top 5 * from linkeddatabase...table I get this error message. [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData (CheckforData()). [Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check your network documentation. Connection B...

Migrating From Access 2000 To SQl Server 2000
Hi, I have developed an application using VB 6 (SP 5), MS Access 2000 and Crystal Reports 9. I migrated from from access to SQl Server 2000. This wasn't a problem as i could import all the tables to SQL Server. Also, i could make VB6 talk to SQl Server. The problem arsies when i run my application. The sql syntax for access seems to be different than that for SQL Server. A simple example being: In access boolean datatype is true/false ,whereas in SQL Server the boolean equivalent is bit (numerical 1 or 0). These kind of issues are causing problems and most queries don't run. Would ...

SQL Server "Linked Server" to MS Access Database
I have a very frustrating problem. I have a situation that works fine on some computers, but not on others. I have SQL Server Express 2005 and Access 2003 loaded on 4 computers. I have set up a "Linked Server" in SQL Server linked to my Access database. I am running queries on t-SQL against the Access tables through the linked server. On all of the computers, I can do select queries and they all work great but what I need to do is run Update, Insert, Delete queries as well. On a couple of the computers, it works fine, locally and across the LAN, and a VPN as far as that goes. But on other computers I can do Select queries (also across the network) but no update queries. I get the error message "OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "securedjetls" returned message "Operation must use an updateable query." where securedjetls is the name of the linked server. The Access database on the computer that is not working is a copy of the database from the computer that is working and any of the tables that I'm trying to update have primary keys, so that's not the problem. ...

Permissions Issue when linking to MS SQL 2000 DB from MS Access XP
Hello, We have a database in MS SQL 2000 and I'm trying to link to it from MS Access XP. I've created a user with full access to one specific table in the MS SQL database, but Access won't write to it. I can open this table in MS Query Analyzer and log-in as the user, and I can do update, delete, and select queries just fine... but when I link to the table from MS Access using the same MS SQL username/password, I can only read the data... no write or delete. Since I can update data fine in MS Query Analyzer, I assume permissions are setup properly. Anyone know why I can not ed...

what is equivalent of format(date) function of MS Access in MS Sql Server 2000
Hi All, I am facing a problem with a sql what i used in MS Access but its not returning the same result in MS Sql Server 2000. Here i am giving the sql: SELECT TOP 3 format( MY_DATE, "dddd mm, yyyy" ) FROM MY_TAB WHERE MY_ID=1 The above sql in ACCESS return me the date in below format in one column: Friday 09, 2003 But in Sql server 2000 i am not getting the same format eventhough i am using convert function, date part function etc. Please if you find the solution would be helpful for me.. Thanks Hoque Hi If you field is a datetime, you can use the CONVERT function to conver...

ODBC connection
Hello all! I have such question to all of you. I have some tables linked from MS SQL Server 2000. Is time of processing query based on these linked tables from MS SQL Server 2000, faster or slower than the time of processing the same query based on tables, which are not linked but imported to MS Access? Can you answer this question? Thank you in advance for posts Marcin from Poland. On Tue, 19 Apr 2005 17:23:48 +0200, "Kamyk" <marcinzmyslowski@poczta.onet.pl> wrote: >Hello all! > >I have such question to all of you. >I have some tables linked from MS SQL Server 2000. Is time of processing >query >based on these linked tables from MS SQL Server 2000, faster or slower than >the time of processing the same query based on tables, which are not linked >but >imported to MS Access? > >Can you answer this question? > >Thank you in advance for posts >Marcin from Poland. > That depends on a lot of things... The most obvious thing it depends on is the size of the result set. In a client/server arrangement, you want to keep result sizes down, and do aggregation and filtering on the server. Next, how many people will be sharing the back-end data. Access slows down as soon as 2 people are sharing a JET back-end, but the server won't have this limitation. On the other hand, if the server is too slow, or you're trying to use MSDE with more users than it's optimized for, the server will be slow. There are m...

Access MS SQL Server 2000?
------_=_NextPart_001_01C3B4EB.13FA7080 Content-Type: text/plain; charset="iso-8859-1" Hello Guys, How can I access MS SQL Server 2000 database from Ruby? Is there a library I can use? Lennie De Villiers ------_=_NextPart_001_01C3B4EB.13FA7080-- Lennie DeVilliers wrote: > Hello Guys, > > How can I access MS SQL Server 2000 database from Ruby? Is there a library I > can use? > > Lennie De Villiers > > Check sqlrelay: http://sqlrelay.sourceforge.net/ I tested it a very short time only (I think with a MS SQL 2000, not even sure;-), so I can...

Accessing a passworded MS Access 2000 database in Crystal Reports
Can anybody give me some code that I can put in to my VB6 app to open a passworded Access database in the Crystal Reports 8.5 RDC? I see so many examples - none of which seem to work for me. I am using a native connection in Crystal ( pdbdao.dll). My current code is : Dim Report As New crPrintPreview Dim crTable As CRAXDRT.DatabaseTable Private Sub Form_Load() Set crTable = Report.Database.Tables.Item(1) varpath = CurDir crTable.Location = varpath & "\OrderManager.mdb" CRViewer1.ReportSource = Report CRViewer1.ViewReport End Sub All I receive is the message "Error opening file xxxxxxx etc" and then "Open Database session failed" Appreciate any help! Peter Tyler ...

MS SQL Server 2000 and MS JDBC SQL Server driver problems. (Connection timed out, followed by Error establishing socket)
I have downloaded the MS SQL Server 2000 Enterprise Evaluation edition, and the MS SQL 2000 Server JDBC driver. I have installed and configured the MS SQL Server 2000 onto two computers, which reside on the same network. (In fact, these two computers comprise the entire network). The network server (computer name=SUNCDS1), and the lone node (SUNCDS2), each have their own SQL Server 2000 instance. The SQL Server 2000 Enterprise Manager console on SUNCDS1 shows both SQL Server instances, the (local) instance, which is SUNCDS1, and also SUNCDS2. It may be worthwhile to mention that the SUNCDS2 instance on the Enterprise console does not show the SQL Server Agent as being started, but I can navigate to the SUNCDS2 databases, and display the table information. If I try to start the SQL Server Agent for SUNCDS2, I receive the message "Access is Denied". The enterprise console on the SUNCDS2 machine indeed shows the SQL Server Agent as being started. I have written a sample Java application that uses both the MS SQL Server ODBC driver and a JDBC type 4 driver (MS SQL Server 2000 JDBC) to establish a connection to the SUNCDS2 instance. This application resides on the SUNCDS1 network server. The ODBC driver will connect to SUNCDS2 instance wihout a problem. The JDBC driver does not connect to SUNCDS2. The JDBC driver will, however, connect to the local SQL Server instance that resides on SUNCDS1. Following are the code snippet to est...

Moving data from MS SQL Server 2000 or MS Access XP to UniData 5.2.9
Complete MVDBMS neophyte -I may not even qualify at the neophyte level- that needs a simple solution to move data -in batch initially...real-time eventually- from RDBMSs such as SQL Server 2000 or Access 2002 (XP) to UniData 5.2.9. Any leads would be greatly appreciated. --Ryan N. Ryan N. wrote: > Complete MVDBMS neophyte -I may not even qualify at the neophyte > level- that needs a simple solution to move data -in batch > initially...real-time eventually- from RDBMSs such as SQL Server 2000 > or Access 2002 (XP) to UniData 5.2.9. > > Any leads would be greatly appreciated. > > --Ryan N. Accuterm, look at the Excel option, and then the scripting to move it on. www.asent.com Better yet, most likely you need a consultant. Check out Tony Gravagno. He's good at building interfaces, and has most of the tools pre-built. He is at http:(no spam)//nebula-rnd.com/ get rid of the (no spam) HTH, Patrick <;=) "(Latimerp)" <"(Latimerp)"@comcast.net> wrote: >Ryan N. wrote: >> Complete MVDBMS neophyte -I may not even qualify at the neophyte >> level- that needs a simple solution to move data -in batch >> initially...real-time eventually- from RDBMSs such as SQL Server 2000 >> or Access 2002 (XP) to UniData 5.2.9. >> >> Any leads would be greatly appreciated. >> >> --Ryan N. > >Accuterm, look at the Excel option, and then t...

Re: Access MS SQL Server 2000?
Hello, Thank you! This page: http://sqlrelay.sourceforge.net/sqlrelay/programming/rubydbi.html Show how to use SQLRelay with Ruby. Lennie De Villiers -----Original Message----- Lennie DeVilliers wrote: > Hello Guys, > > How can I access MS SQL Server 2000 database from Ruby? Is there a library I > can use? > > Lennie De Villiers > > Check sqlrelay: http://sqlrelay.sourceforge.net/ I tested it a very short time only (I think with a MS SQL 2000, not even sure;-), so I can't say more about it. Raph ...

Database link to a MS SQL Server 2000
Hi NG, we have to replikate some data between an oracle DB and an application with MS SQL Server. There is no possibility to load and push the data on the client mashine. We have to replicate the data on the server mashines directly. Is there a possibility to create a DB link from oracle to MS SQL Server or otherwise? thanx abbas On Thu, 9 Jun 2005 00:13:02 +0200, "Abbas" <ar.newsgroup@web.de> wrote: >Hi NG, > >we have to replikate some data between an oracle DB and an application with MS SQL Server. > >There is no possibility to load and push the data on the client mashine. We have to replicate the data on the server mashines >directly. > >Is there a possibility to create a DB link from oracle to MS SQL Server or otherwise? > >thanx >abbas > Look into using Heterogeneous Services in your Net administrators manual. -- Sybrand Bakker, Senior Oracle DBA Are you willing to install a bridge program to create this "link"? If so, then try www.examinstudio.com Do as Sybrand told you, look at the Heterogenous services of Oracle. Oracle even supports natively DRDA towards a DB2 z/OS mainframe, and they also support SQL Server. They all end-up like a database link with a totally quallified names in the "from" clause. Be aware of that distributed joins between different DBMS systems might cause huge performance problems, keep the functionality when dealing with multiple DBMS systems simple, no dist...

ODBC Link from Access 2000 & XP to SQL 2000 problems
Hello, I am converting an Access database on our network to a sql 2000 backend and keeping access as the front end. The access database has evolved and been a solution to collect data but now we want to move the tables over to sql 2000. We have successfully done that but are now having difficulties with an ODBC connection. Initiall linking the tables is not an issue and I am able to connect with no problem. However after closing down the ODBC database and then going back in the ODBC connection doesnt get refreshed and errors out when you try to open the table. It then asks to redo connection. Because we have over 40 people in this file, we want to make the ODBC connection authenticate perhaps in the autoexec macro or the main form Load event so people dont keep getting this error. Does anyone know if there is a way to refresh the ODBC connection each time you logon prompting for user name and password on the main form load event or something. To have people go into the refresh linked tables ect...would be way over their head. Thanks, Paul You need to delete all of the current ODBC tables in the database and relink the SQL tables. You should also check to make sure each user has the same SQL ODBC driver, and ODBC DSN name. If the name doesn't match, then you will have issues. We had a SQL database with 40 tables and a few tables had >2 million records. What you are wanting to do is definitely feasible. TJ Paul wrote: > Hello, > > I am converting...

Accessing a passworded MS Access 2000 database in Crystal Reports #2
Can anybody give me some code that I can put in to my VB6 app to open a passworded Access database in the Crystal Reports 8.5 RDC? I see so many examples - none of which seem to work for me. I am using a native connection in Crystal ( pdbdao.dll). My current code is : Dim Report As New crPrintPreview Dim crTable As CRAXDRT.DatabaseTable Private Sub Form_Load() Set crTable = Report.Database.Tables.Item(1) varpath = CurDir crTable.Location = varpath & "\OrderManager.mdb" CRViewer1.ReportSource = Report CRViewer1.ViewReport End Sub All I receive is the message "Error opening file xxxxxxx etc" and then "Open Database session failed" Appreciate any help! Peter Tyler ...

Performance Issues
Hi, Simple question: A customer has an application using Access 2000 frontend and SQL Server 2000 backend. Data connection is over ODBC. There are almost 250 concurrent users and is growing. Have they squeezed everything out of Access? Should the move to a VB.Net frontend taken place ages ago? Cheers Mike Hi Mike, I just completed a similar conversion if that's what you're thinking of doing. We converted 100 forms, 60 access reports and 15,000 lines of code "TO" 100 forms, 60 crystal reports, and 75,000 lines of VB.net code. It was a nightmare. We spent a good 50 perc...

Problem mit der Abfrageansicht bei Access 2000 / SQL Server 2000
Ich habe eine Datenbank die vorher komplett in Access war. Jetzt habe ich die Tabellen von Access auf SQL-Server portiert und in die Access-Datenbank als Frontend verkn�pft. Jetzt habe ich folgendes Problem: Vorher als die Tabelle noch in der Access-Datenbank war konnte ich die Tabelle (oder auch eine Abfrage darauf) �ffnen und wenn ich durch die Tabelle gescrollt habe indem ich den Scrollbalken auf der rechten bewegt habe (nicht mit dem Mausrad!) wurden w�hrend des Scrollens die Inhalte der Datens�tze in echtzeit mitbewegt. Bei der Variante wo die Tabellen jetzt auf dem SQL-Server liegen ist es nun so, dass der Inhalt der Datens�tze erst dann angezeigt wird, wenn ich mit dem Scrollbalken wieder anhalte (w�hrend des Scrollvorgangs selber wird also immer das Bild mit den Ursprungsdatens�tzen angezeigt) Hat jemand eine Idee ob es m�glich ist, die "alte" Funktionalit�t durch Einstellungen von Access oder SQL-Server wieder hin zu kriegen? Ich habe schon nach Einstellungen gesucht, die bewirken, dass man beim �ffnen sofort die ganze Tabelle geladen wird oder sowas �hnliches, aber ich hab nichts derartiges gefunden. Vielen Dank im Voraus. Matthias J�rgens ...

Using both IS NULL and IS NOT NULL in Access 2000 Pass Through Query (for SQL Server 2000)
Hello, I'm in the process of changing our 'normal' Access 2000 update queries to Update Pass Through Queries. We have a SQL server 2000 database and we're using an Access 2000 database as our front end. In the criteria of one of our update query fields, we use both the isnull(field1) and Not IsNull(field2) then Field3, otherwise, set the updated field as null. Here's my converted Pass Through Query : UPDATE Vsel SET VSel.Cert1 = IIf(IsNull(VSel.Cert1), RenArch.Cert1), VSel.Cert2 = IIf(IsNull(VSel.Cert2), RenArch.Cert2), VSel.Cert3 = IIf(IsNull(VSel.Cert3), RenArch.Cert3), VSel.Cert4 = IIf(IsNull(VSel.Cert4), RenArch.Cert4), VSel.Cert1ExpDt = IIf(IsNull(BDACmpltDt) AND IS NOT NULL (RenArch.Cert1), RenArch.CDDt), VSel.Cert2ExpDt = IIf(IsNull(BOMSADtIssd) AND IS NOT NULL (RenArch.Cert2), RenArch.CDDt), VSel.Cert3ExpDt = IIf(IsNull(BDPMADtIssd) AND IS NOT NULL (RenArch.Cert3), RenArch.CDDt), VSel.Cert4ExpDt = IIf(IsNull(BOACmpltDt) AND IS NOT NULL (RenArch.Cert4), RenArch.CDDt), VSel.BDACmpltDt = IIf(IsNull(BDACmpltDt), RenArch.Cert1DOC), VSel.BOACmpltDt = IIf(IsNull(BOACmpltDt), RenArch.Cert4DOC), VSel.BDPMADtIssd = IIf(IsNull(BDPMADtIssd), RenArch.Cert3DOC), VSel.BOMSADtIssd = IIf(IsNull(BOMSADtIssd), RenArch.Cert2DOC) FROM VSel INNER JOIN RenArch ON VSel.SSN = RenArch.SSN This is the original criteria in the access update query for line #6 (when I start to use both is null and is not null) IIf(IsNull([BDACmpltDt]) And Not IsNull([RenArch].[Cert1]),[Re...

Possible to keep MS Access interface and migrate the MS Access to MS SQL Server?
Hello all Would it be possible to migrate the MS Access 2000 to MS SQL Server 2000? My application is using MS Access 2000 as database and as user interface such as forms. Now, I want to migrate the backend database from MS Access 2000 to MS SQL Server 2000. However, I want to keep the MS Access 2000 interface. Would it be possible? If I migrate the MS Access to SQL Server, would the queries, back-end VBA, macro, tables and forms be affected? Do I need to change the MS Access data type to SQL server supported data type? Which tool I can use to do the migration? Upsizing wizard or exporting...

Access 2000 Forms with Linked Tables to Microsoft SQL Server
I have a Sql table linked into Access 2000 where we use Access as the front end to our SQL server. I have a table that has been imported from a previous version of Access. I have created a form with a combo box to find records on in my table. The form works perfectly on old data in the same table. When I use the form to find recently added records and make a change and move to a new record by selecting new text in the combo box, I get an error that says Write Conflict. The record has been changed by another user since you started editing it. If you save the record you will overwrite the changes the other user made. I know for a fact, I am the only user. When I debug my code I see Private Sub Combo76_AfterUpdate() ' Find the record that matches the control. Dim rs As Object Set rs = Me.Recordset.Clone rs.FindFirst "[Id] = " & Str(Nz(Me![Combo76], 0)) If Not rs.EOF Then Me.Bookmark = rs.Bookmark End Sub Where Me.Bookmark = rs.Bookmark is highlighted. This code works for older records but not new ones. I have recreated the Id field which is the primary key for the table and still no help. Looking for suggestions.... Thanks in advance. Somewhere in that table is a BIT field w/o a value...here's what you need to do: 1) Run an UPDATE query, setting all NULL bit fields to 0. 2) On the SQL Server side, set a default value of 0 for the BIT fields. (or, on the Access side, make sure you provide the value) dcmfan@aol.comSPNOAM (D...

Web resources about - Access 2000 Linked to SQL Server 2000, openRecordset error - comp.databases.ms-access

Wikipedia:Reference desk/all - Wikipedia, the free encyclopedia
This page lists all the recent questions asked on the Wikipedia reference desk by category. To ask a new question, please select one of the categories ...

Wikipedia:Reference desk/all - Wikipedia, the free encyclopedia
This page lists all the recent questions asked on the Wikipedia reference desk by category. To ask a new question, please select one of the categories ...

Resources last updated: 3/21/2016 9:36:55 PM