accessing database problem

Hello friends when I am working in VWD and accessing sql data this error came?

I am using asp.net visual web developer edition and sql server 2005 expressedition.

plz check it out and help me.

The log scan number (588:85:1) passed to log scan in database 'D:\GCAP\APP_DATA\GRIET_IT.MDF' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.
Could not open new database 'D:\GCAP\APP_DATA\GRIET_IT.MDF'. CREATE DATABASE is aborted.
An attempt to attach an auto-named database for file D:\GCAP\App_Data\GRIET_IT.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: The log scan number (588:85:1) passed to log scan in database 'D:\GCAP\APP_DATA\GRIET_IT.MDF' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.
Could not open new database 'D:\GCAP\APP_DATA\GRIET_IT.MDF'. CREATE DATABASE is aborted.
An attempt to attach an auto-named database for file D:\GCAP\App_Data\GRIET_IT.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): The log scan number (588:85:1) passed to log scan in database 'D:\GCAP\APP_DATA\GRIET_IT.MDF' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.
Could not open new database 'D:\GCAP\APP_DATA\GRIET_IT.MDF'. CREATE DATABASE is aborted.
An attempt to attach an auto-named database for file D:\GCAP\App_Data\GRIET_IT.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.]
 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +171
 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +199
 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2406
 System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +34
 System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +223
 System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +371
 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +184
 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +193
 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +501
 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +429
 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +70
 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +510
 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +85
 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +89
 System.Data.SqlClient.SqlConnection.Open() +159
 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +118
 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +139
 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +82
 System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1653
 System.Web.UI.WebControls.ListControl.OnDataBinding(EventArgs e) +82
 System.Web.UI.WebControls.ListControl.PerformSelect() +18
 System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +68
 System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +61
 System.Web.UI.WebControls.ListControl.OnPreRender(EventArgs e) +26
 System.Web.UI.Control.PreRenderRecursiveInternal() +88
 System.Web.UI.Control.PreRenderRecursiveInternal() +171
 System.Web.UI.Control.PreRenderRecursiveInternal() +171
 System.Web.UI.Control.PreRenderRecursiveInternal() +171
 System.Web.UI.Control.PreRenderRecursiveInternal() +171
 System.Web.UI.Control.PreRenderRecursiveInternal() +171
 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5684

---

Version Information: Microsoft .NET Framework Version:2.0.50727.832; ASP.NET Version:2.0.50727.832

Hi radhekrishna,

The error message indicates that either the database file you are trying to attached has already been attached in sql express or there is data corruption in your database file(.mdf file) , thus the db file could not be attached. I would suggest you connect to your sql express server through management studio to make a verification (Note here that since Sql Express use customer instance, in management studio, you must login using the same account as the one you used in your application). If that db file has been attached, detach it and take another try.

If there is no such file attached already in your sql express, I would suggest you first attach that mdf file to your database. If you still get this error, it means that the mdf file is data corrupted (information in ldf file and mdf file does not match) and cannot be used anymore--You need to restore it then.

Hope my suggestion helps

Accessing database in STANDBY mode while log shipping

Hi,

I am testing with Log shipping, I have it setup and it is working just fine. The secondary database was restored with Standby mode and is readonly. I have no problem accessing and querying data from the secondary database but I noticed if changes are made to the primary and I am accessing the database when the restore step to the secondary runs it fails with the following error:

Message
2006-07-31 09:40:54.33 *** Error: Could not apply log backup file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\LogShip\CI_REPLICATION_TEST_20060731131501.trn' to secondary database 'CI_REPLICATION_TEST'.(Microsoft.SqlServer.Management.LogShipping) ***
2006-07-31 09:40:54.33 *** Error: Exclusive access could not be obtained because the database is in use.
RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) ***

This appears to be because you can not restore to a database that is in use? How do you get around this? Is it possible? How do I query data from the secondary db without worrying about causing log shipping to fail? Do I not worry about it because at somepoint when Exclusive access is obtainable all the logs will be rolled forward, but this could create a wide gap in sychronization between the primary and secondary db's, say if someone or something does not properly log off. I guess I could disconnect all users prior to applying tlogs but that kind of defeats the purpse of having it in read only mode so that data can be accessed? Couldn't really depend on it as a stable source of data for running data extracts? I suppose it could all just come down to timing everything in the appropriate sequence? Anyway.....

To sum up:

Is there anyway to access the standby server without causing the restore process to fail?

Both the Primary and Secondary are SQL 2K5.

Thanks!

Quick answer? No.

To load the t-logs, SQL Server needs exclusive access to the DB. So:

a) you allow read only access to continue, which can potentially block the t-log load

b) allow the t-log load, knowing that users will not be able to access the DB while the t-log is loading

c) kill the connections to the DB when the t-log is about to load, effectively creating b), but making unhappy end users

d) use another reporting solution

Log shipping has always been meant for creating a standby. While you can use it for read only access, it is not the best use of log shipping, and never has been. I have never ever recommended this configuration. YOu are better off setting up another way to do reporting and leave log shipping alone.

This is just the way the SQL engine works.

|||

Thanks!

Your basicly confirming, I guess, what I already knew. Just wanted to make sure I understood correctly.

access update query

I have an Access update query that is not working.

Here's my SQL:UPDATE tablename SET tablename.HeartAttackDOD = " "
WHERE(((tablename.HeartAttackDOD)=#1/1/2001#) AND ((tablename.HeartAttack)="2"));

It sets HeartAttackDOD = " " even if HeartAttack = "1" and I only want it to set HeartAttackDOD = " " if HeartAttack = "2".

Can anyone help?

Is ' tablename.HeartAttack ' a string or number?

You are evaluating against strings.

Access Projects, SQL Server, and 1-to-1 joins

Hello everyone,

I'm working on porting an Access Application with linked tables to an Access Project, to make it faster. The backend is (unsurprisingly) MS SQL Server.

My problem is thisL:

I have a form (well, several forms) which are sourced from a 1 - 1 join
Person <-> Candidate

It seems that I can only edit one of these tables at a time, the one specified by the UniqueTable property. I want to have access to the whole record at once. In the Access documentation, it doesn't seem to mention this case, which seems a little odd.

Adding records is not a problem, it's only updating, and it can be guaranteed that a Clienta and Person record both exist.

I don't think I can enforce referential integrety, as Person also links to Client, so a Person record might not match to a Candidate record.

How can I set up the database or form to permit the editing of both tables simulatenously?

Thanks,
SamJust a suggestion but while you are porting this thing over why not de-normalize these tables into one table with a Type field (person, client, candidate)?

This will make life much easier...|||The structure is basically trying to mimic OO inheritance. The problem is that Candidates and Clients have too many similar fields to be put into completely separate tables (it would also make _really_ wide tables, which is a bit of a no-no), but too many different fields to make one table. apart from an even wider table, there would be heaps of empty fields.

While it would be easer, sometimes the easiest solution isn't the best. And it seems completely ridiculous that this is an unsolvable problem. I mean, it worked just fine in Jet, why not in it's bigger brother?|||Hi,
I was searching and found your thread. I am having the exact same problem. I have a "Person" table and other subtypes of it like "Contatc".
On my contact form I have a record source with a 1-1 join on tblContact and tblPerson (when a user is inserting a contact a VB code would insert the new assigned number from the Person table to the Contact table)

Now that I'm trying to use Access projects it won't work. Its driving me nuts. I've tried every trick I know and it won't work.

By the way I am very suprised that my ODBC connection is much faster than my Access project, at least for data entry. When I'm using Access project and when I start typing in a field it pause for a second and then the characters apear. Thats wierd, do you know any thing about it?

Originally posted by Digitaleus
The structure is basically trying to mimic OO inheritance. The problem is that Candidates and Clients have too many similar fields to be put into completely separate tables (it would also make _really_ wide tables, which is a bit of a no-no), but too many different fields to make one table. apart from an even wider table, there would be heaps of empty fields.

While it would be easer, sometimes the easiest solution isn't the best. And it seems completely ridiculous that this is an unsolvable problem. I mean, it worked just fine in Jet, why not in it's bigger brother?|||Answer is as simple as bad: it can't be done. You can only update the table specified by UniqueTableProperty (which must be set programmatically if you use storep procedures, btw).

There are mainly two ways to get around this:
a) split the form in two, syncing them on the appropriate id and update form-wise or
b) use unbound forms and display/update programatically.

Which one is less inconvinient depends on your specific needs.|||You mean instead of having a join query as the record source of the form, us a wizard to sync them (we can also drag and drop)?

Thanks

Originally posted by chrisp_999
Answer is as simple as bad: it can't be done. You can only update the table specified by UniqueTableProperty (which must be set programmatically if you use storep procedures, btw).

There are mainly two ways to get around this:
a) split the form in two, syncing them on the appropriate id and update form-wise or
b) use unbound forms and display/update programatically.

Which one is less inconvinient depends on your specific needs.|||Assume you have frm_person and frm_contact as subform of frm_contact.

Datasource for frm_contact is
select * from tbl_person

Assumed you have a field ID in tbl_person, datasource for frm_contact is

select * from tbl_contact where personID = @.ID

and the Input Parameter property for frm_contact is

@.ID int = forms!frm_person!ID
(or txt_ID or whatever the name of the field is)

Anytime the record on frm_person is changed, issue (in VBA)
forms!frm_contact.requery.

That's the basic scheme. Details vary depending on wether you have an endless form or not and so on.

BTW, with MSSQL you can use a trigger to create the entry in tbl_contact instead of VBA. So you ensure data integrity within the database (e.g. if you insert manually).|||correction :-/

forms!frm_contact.requery must read forms!frm_person!frm_contact.form.requery

as frm_contact is a subform ...

I should reread before posting.|||Thanks

Originally posted by chrisp_999
correction :-/

forms!frm_contact.requery must read forms!frm_person!frm_contact.form.requery

as frm_contact is a subform ...

I should reread before posting.

Access Locking problem

I've been working on this problem for days and cannot seem to figure it out. I'm using the Microsoft Access Driver in an ASP web page to try to update a database. I'm using adLockPessimistic, but it is acting like I'm using adLockOptimistic.

Before you ask, I'm using MDAC 2.8 SP2 on my Windows 2003 Server.

If I open two web pages at the same time, both trying to increment a counter in a database 100 times, the end result should be a value of 200. But in most trials, I get a number less than 200, like 187. It almost seems like it is not locked at all, ecept for the fact that I sometimes get an error telling me I can't update the data because it is locked by another user.

So... I can read OK, but not save because sometimes its locked by another user and sometimes the increment doesn't really increment, therefore it seems like I have adLockOptimistic instead of adLockPessimistic. I do get adLockPessimistic when I read back the lock type.

Any help would be great!

Code Snippet

for i = 1 to 100
if ( len(Request.Form("zipcode")) > 0 ) then
call LogImpression (38)
end if
next

Function LogImpression (CampID)
'This increments the impression counter for a campaign in the vendors database
On Error Resume Next

Set adoCon = Server.CreateObject("ADODB.Connection")
'adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("accessdb/vendors.mdb")
adoCon.Open "DSN=vendorDB"
Set rsAddComments = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT vendors.impressions,vendors.ID FROM vendors WHERE ID=" & CampID & ";"
rsAddComments.CursorType = 2
rsAddComments.LockType = 2
rsAddComments.CursorLocation = 2

rsAddComments.Open strSQL, adoCon, 2, 2, adCmdTableDirect
if (NOT rsAddComments.EOF) then
Err.Clear

MyCount=CInt(rsAddComments.Fields("impressions")) + 1
rsAddComments.Fields("impressions") = MyCount
rsAddComments.Update

if Err.Number <> 0 then
'we had an error, row was locked
Err.Clear

MyCount=CInt(rsAddComments.Fields("impressions")) + 1
rsAddComments.Fields("impressions") = MyCount

rsAddComments.Update
end if

end if
rsAddComments.Close

Set rsAddComments = Nothing
Set adoCon = Nothing
end function

Thanks, Lee

Your code itself doesn't lock this critical section. You cannot consider these two statements to be atomic. You have two database operations going on, and you are trying to run them atomically, so your code needs to lock around that area or you need to wrap the commands in transactions.

Here is the scenario you have right now:

Assume that the database starts with "impressions" set to 1:

process A updates MyCount = impressions + 1 = 2 then context switches out

process B updates MyCount = impressions + 1 = 2 then context switches out

process A sets impressions = MyCount = 2 then context switches out

process B sets impressions = MyCount = 2 then context switches out

These concurrency issues do not happen all the time, and are hard to reproduce, but this is clearly an application level concurrency issue.

I suggest wrapping those two lines in a critical section to enforce serialization there.

Hope that helps,

John

Access is denied: 'Interop.ADODB'.

I am using a com component in my asp.net programme and it was working fine for many days . now I am getting following error .

Source Error:

Line 196: <add assembly="System.EnterpriseServices, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>Line 197: <add assembly="System.Web.Mobile, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>Line 198: <add assembly="*"/>Line 199: </assemblies>Line 200: </compilation>

Source File: c:\windows\microsoft.net\framework\v1.1.4322\Config\machine.config Line: 198

Assembly Load Trace: The following information can be helpful to determine why the assembly 'Interop.ADODB' could not be loaded.

=== Pre-bind state information ===LOG: DisplayName = Interop.ADODB (Partial)LOG: Appbase = file:///e:/inetpub/wwwroot/SAPTRainingLOG: Initial PrivatePath = binCalling assembly : (Unknown).=== LOG: Policy not being applied to reference at this time (private, custom, partial, or location-based assembly bind).LOG: Post-policy reference: Interop.ADODBLOG: Attempting download of new URL file:///C:/WINDOWS/Microsoft.NET/Framework/v1.1.4322/Temporary ASP.NET Files/saptraining/8932fe97/1bed5ea1/Interop.ADODB.DLL.LOG: Attempting download of new URL file:///C:/WINDOWS/Microsoft.NET/Framework/v1.1.4322/Temporary ASP.NET Files/saptraining/8932fe97/1bed5ea1/Interop.ADODB/Interop.ADODB.DLL.LOG: Attempting download of new URL file:///e:/inetpub/wwwroot/SAPTRaining/bin/Interop.ADODB.DLL.LOG: Policy not being applied to reference at this time (private, custom, partial, or location-based assembly bind).LOG: Post-policy reference: Interop.ADODB, Version=2.6.0.0, Culture=neutral, PublicKeyToken=null

---

Version Information: Microsoft .NET Framework Version:1.1.4322.2300; ASP.NET Version:1.1.4322.2300

How this can be solved ? Please help

Something has probably changed.

Check that the config file still contains a valid configuration, and that the file(s) that are needed for your app still are at the expected place(s). ie paths, filenames etc...

/Kenneth

Access Functions not Working

Hi--
We just upgraded our server and now have Sql 2005 as a backend to our
Access ADP. With SQL 2000, we had used functions like Date() to provide
default values for certain fields. Now, these functions are not working
on the clients. However, they do work on every computer with SQL 2005
installed. I have 2005 installed on my laptop and the server and am
having no problems on those computers. Is there anything I can do to
the clients to resolve this issue' Thanks in advance.
ChrisDate() is VB/VBA function that only runs inside front app's code (ADP's VBA
code). It has nothing to do with back end, be it SQL Server2000 or SQL
Server2005. If you used Date() in backend query (SP, View...) it should not
have worked at all, because SQL Server2000/2005 would not recognize it.
Your issue is most likely cuased by missing reference(s) that is required by
your ADP app itself. Go to VBA Editor and click menu "Tools->References..."
to looking for missing reference(s).
<creejohnson@.gmail.com> wrote in message
news:1159308950.973840.54110@.i3g2000cwc.googlegroups.com...
> Hi--
> We just upgraded our server and now have Sql 2005 as a backend to our
> Access ADP. With SQL 2000, we had used functions like Date() to provide
> default values for certain fields. Now, these functions are not working
> on the clients. However, they do work on every computer with SQL 2005
> installed. I have 2005 installed on my laptop and the server and am
> having no problems on those computers. Is there anything I can do to
> the clients to resolve this issue' Thanks in advance.
> Chris
>|||Thanks, Norman.
I am aware that Date() is a vba function, but it DID work with sql 2000
and does not work with Sql 2005. It is used only in the front end as a
default value of a text box. That is what is mystifying me. There are
no missing references..already checked that. Any other ideas? The thing
that really makes me wonder is that the defaults work on those
computers that have the client tools/legacy components installed for
sql 2005.
Chris
Norman Yuan wrote:[vbcol=seagreen]
> Date() is VB/VBA function that only runs inside front app's code (ADP's VB
A
> code). It has nothing to do with back end, be it SQL Server2000 or SQL
> Server2005. If you used Date() in backend query (SP, View...) it should no
t
> have worked at all, because SQL Server2000/2005 would not recognize it.
> Your issue is most likely cuased by missing reference(s) that is required
by
> your ADP app itself. Go to VBA Editor and click menu "Tools->References...
"
> to looking for missing reference(s).
> <creejohnson@.gmail.com> wrote in message
> news:1159308950.973840.54110@.i3g2000cwc.googlegroups.com...|||It is hard to debug "not working". Possibly that function result in date bei
ng passed as a string to
SQL Server, and different string formats being messed up due to language set
tings. Use Profiler to
see what is really submitted to SQL Server. Also see
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<creejohnson@.gmail.com> wrote in message news:1159308950.973840.54110@.i3g2000cwc.googlegroup
s.com...
> Hi--
> We just upgraded our server and now have Sql 2005 as a backend to our
> Access ADP. With SQL 2000, we had used functions like Date() to provide
> default values for certain fields. Now, these functions are not working
> on the clients. However, they do work on every computer with SQL 2005
> installed. I have 2005 installed on my laptop and the server and am
> having no problems on those computers. Is there anything I can do to
> the clients to resolve this issue' Thanks in advance.
> Chris
>|||Thanks, Tibor.
I guess I should be more specific on the problem, but by not working, I
mean not working:-) There is nothing in any textbox using the Date()
VBA function when there should be (used to be before the switch)
today's date.
I have a couple of ideas to check into tomorrow, but any help you could
give would be appreciated.
Thanks
Chris
Tibor Karaszi wrote:[vbcol=seagreen]
> It is hard to debug "not working". Possibly that function result in date b
eing passed as a string to
> SQL Server, and different string formats being messed up due to language s
ettings. Use Profiler to
> see what is really submitted to SQL Server. Also see
> http://www.karaszi.com/SQLServer/info_datetime.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <creejohnson@.gmail.com> wrote in message news:1159308950.973840.54110@.i3g2
000cwc.googlegroups.com...|||Seems to be an Access issue, then. I suggest you ask this in an Access group
, as we are more into
the engine here... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<creejohnson@.gmail.com> wrote in message
news:1159404812.588775.157020@.m7g2000cwm.googlegroups.com...
> Thanks, Tibor.
> I guess I should be more specific on the problem, but by not working, I
> mean not working:-) There is nothing in any textbox using the Date()
> VBA function when there should be (used to be before the switch)
> today's date.
> I have a couple of ideas to check into tomorrow, but any help you could
> give would be appreciated.
> Thanks
> Chris
> Tibor Karaszi wrote:
>

Access Functions not Working

Hi--
We just upgraded our server and now have Sql 2005 as a backend to our
Access ADP. With SQL 2000, we had used functions like Date() to provide
default values for certain fields. Now, these functions are not working
on the clients. However, they do work on every computer with SQL 2005
installed. I have 2005 installed on my laptop and the server and am
having no problems on those computers. Is there anything I can do to
the clients to resolve this issue? Thanks in advance.
Chris
Date() is VB/VBA function that only runs inside front app's code (ADP's VBA
code). It has nothing to do with back end, be it SQL Server2000 or SQL
Server2005. If you used Date() in backend query (SP, View...) it should not
have worked at all, because SQL Server2000/2005 would not recognize it.
Your issue is most likely cuased by missing reference(s) that is required by
your ADP app itself. Go to VBA Editor and click menu "Tools->References..."
to looking for missing reference(s).
<creejohnson@.gmail.com> wrote in message
news:1159308950.973840.54110@.i3g2000cwc.googlegrou ps.com...
> Hi--
> We just upgraded our server and now have Sql 2005 as a backend to our
> Access ADP. With SQL 2000, we had used functions like Date() to provide
> default values for certain fields. Now, these functions are not working
> on the clients. However, they do work on every computer with SQL 2005
> installed. I have 2005 installed on my laptop and the server and am
> having no problems on those computers. Is there anything I can do to
> the clients to resolve this issue? Thanks in advance.
> Chris
>
|||Thanks, Norman.
I am aware that Date() is a vba function, but it DID work with sql 2000
and does not work with Sql 2005. It is used only in the front end as a
default value of a text box. That is what is mystifying me. There are
no missing references..already checked that. Any other ideas? The thing
that really makes me wonder is that the defaults work on those
computers that have the client tools/legacy components installed for
sql 2005.
Chris
Norman Yuan wrote:[vbcol=seagreen]
> Date() is VB/VBA function that only runs inside front app's code (ADP's VBA
> code). It has nothing to do with back end, be it SQL Server2000 or SQL
> Server2005. If you used Date() in backend query (SP, View...) it should not
> have worked at all, because SQL Server2000/2005 would not recognize it.
> Your issue is most likely cuased by missing reference(s) that is required by
> your ADP app itself. Go to VBA Editor and click menu "Tools->References..."
> to looking for missing reference(s).
> <creejohnson@.gmail.com> wrote in message
> news:1159308950.973840.54110@.i3g2000cwc.googlegrou ps.com...
|||It is hard to debug "not working". Possibly that function result in date being passed as a string to
SQL Server, and different string formats being messed up due to language settings. Use Profiler to
see what is really submitted to SQL Server. Also see
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<creejohnson@.gmail.com> wrote in message news:1159308950.973840.54110@.i3g2000cwc.googlegrou ps.com...
> Hi--
> We just upgraded our server and now have Sql 2005 as a backend to our
> Access ADP. With SQL 2000, we had used functions like Date() to provide
> default values for certain fields. Now, these functions are not working
> on the clients. However, they do work on every computer with SQL 2005
> installed. I have 2005 installed on my laptop and the server and am
> having no problems on those computers. Is there anything I can do to
> the clients to resolve this issue? Thanks in advance.
> Chris
>
|||Thanks, Tibor.
I guess I should be more specific on the problem, but by not working, I
mean not working:-) There is nothing in any textbox using the Date()
VBA function when there should be (used to be before the switch)
today's date.
I have a couple of ideas to check into tomorrow, but any help you could
give would be appreciated.
Thanks
Chris
Tibor Karaszi wrote:[vbcol=seagreen]
> It is hard to debug "not working". Possibly that function result in date being passed as a string to
> SQL Server, and different string formats being messed up due to language settings. Use Profiler to
> see what is really submitted to SQL Server. Also see
> http://www.karaszi.com/SQLServer/info_datetime.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <creejohnson@.gmail.com> wrote in message news:1159308950.973840.54110@.i3g2000cwc.googlegrou ps.com...
|||Seems to be an Access issue, then. I suggest you ask this in an Access group, as we are more into
the engine here... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<creejohnson@.gmail.com> wrote in message
news:1159404812.588775.157020@.m7g2000cwm.googlegro ups.com...
> Thanks, Tibor.
> I guess I should be more specific on the problem, but by not working, I
> mean not working:-) There is nothing in any textbox using the Date()
> VBA function when there should be (used to be before the switch)
> today's date.
> I have a couple of ideas to check into tomorrow, but any help you could
> give would be appreciated.
> Thanks
> Chris
> Tibor Karaszi wrote:
>

Access Functions not Working

Hi--
We just upgraded our server and now have Sql 2005 as a backend to our
Access ADP. With SQL 2000, we had used functions like Date() to provide
default values for certain fields. Now, these functions are not working
on the clients. However, they do work on every computer with SQL 2005
installed. I have 2005 installed on my laptop and the server and am
having no problems on those computers. Is there anything I can do to
the clients to resolve this issue' Thanks in advance.
ChrisDate() is VB/VBA function that only runs inside front app's code (ADP's VBA
code). It has nothing to do with back end, be it SQL Server2000 or SQL
Server2005. If you used Date() in backend query (SP, View...) it should not
have worked at all, because SQL Server2000/2005 would not recognize it.
Your issue is most likely cuased by missing reference(s) that is required by
your ADP app itself. Go to VBA Editor and click menu "Tools->References..."
to looking for missing reference(s).
<creejohnson@.gmail.com> wrote in message
news:1159308950.973840.54110@.i3g2000cwc.googlegroups.com...
> Hi--
> We just upgraded our server and now have Sql 2005 as a backend to our
> Access ADP. With SQL 2000, we had used functions like Date() to provide
> default values for certain fields. Now, these functions are not working
> on the clients. However, they do work on every computer with SQL 2005
> installed. I have 2005 installed on my laptop and the server and am
> having no problems on those computers. Is there anything I can do to
> the clients to resolve this issue' Thanks in advance.
> Chris
>|||Thanks, Norman.
I am aware that Date() is a vba function, but it DID work with sql 2000
and does not work with Sql 2005. It is used only in the front end as a
default value of a text box. That is what is mystifying me. There are
no missing references..already checked that. Any other ideas? The thing
that really makes me wonder is that the defaults work on those
computers that have the client tools/legacy components installed for
sql 2005.
Chris
Norman Yuan wrote:
> Date() is VB/VBA function that only runs inside front app's code (ADP's VBA
> code). It has nothing to do with back end, be it SQL Server2000 or SQL
> Server2005. If you used Date() in backend query (SP, View...) it should not
> have worked at all, because SQL Server2000/2005 would not recognize it.
> Your issue is most likely cuased by missing reference(s) that is required by
> your ADP app itself. Go to VBA Editor and click menu "Tools->References..."
> to looking for missing reference(s).
> <creejohnson@.gmail.com> wrote in message
> news:1159308950.973840.54110@.i3g2000cwc.googlegroups.com...
> > Hi--
> >
> > We just upgraded our server and now have Sql 2005 as a backend to our
> > Access ADP. With SQL 2000, we had used functions like Date() to provide
> > default values for certain fields. Now, these functions are not working
> > on the clients. However, they do work on every computer with SQL 2005
> > installed. I have 2005 installed on my laptop and the server and am
> > having no problems on those computers. Is there anything I can do to
> > the clients to resolve this issue' Thanks in advance.
> >
> > Chris
> >|||It is hard to debug "not working". Possibly that function result in date being passed as a string to
SQL Server, and different string formats being messed up due to language settings. Use Profiler to
see what is really submitted to SQL Server. Also see
http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<creejohnson@.gmail.com> wrote in message news:1159308950.973840.54110@.i3g2000cwc.googlegroups.com...
> Hi--
> We just upgraded our server and now have Sql 2005 as a backend to our
> Access ADP. With SQL 2000, we had used functions like Date() to provide
> default values for certain fields. Now, these functions are not working
> on the clients. However, they do work on every computer with SQL 2005
> installed. I have 2005 installed on my laptop and the server and am
> having no problems on those computers. Is there anything I can do to
> the clients to resolve this issue' Thanks in advance.
> Chris
>|||Thanks, Tibor.
I guess I should be more specific on the problem, but by not working, I
mean not working:-) There is nothing in any textbox using the Date()
VBA function when there should be (used to be before the switch)
today's date.
I have a couple of ideas to check into tomorrow, but any help you could
give would be appreciated.
Thanks
Chris
Tibor Karaszi wrote:
> It is hard to debug "not working". Possibly that function result in date being passed as a string to
> SQL Server, and different string formats being messed up due to language settings. Use Profiler to
> see what is really submitted to SQL Server. Also see
> http://www.karaszi.com/SQLServer/info_datetime.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <creejohnson@.gmail.com> wrote in message news:1159308950.973840.54110@.i3g2000cwc.googlegroups.com...
> > Hi--
> >
> > We just upgraded our server and now have Sql 2005 as a backend to our
> > Access ADP. With SQL 2000, we had used functions like Date() to provide
> > default values for certain fields. Now, these functions are not working
> > on the clients. However, they do work on every computer with SQL 2005
> > installed. I have 2005 installed on my laptop and the server and am
> > having no problems on those computers. Is there anything I can do to
> > the clients to resolve this issue' Thanks in advance.
> >
> > Chris
> >|||Seems to be an Access issue, then. I suggest you ask this in an Access group, as we are more into
the engine here... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<creejohnson@.gmail.com> wrote in message
news:1159404812.588775.157020@.m7g2000cwm.googlegroups.com...
> Thanks, Tibor.
> I guess I should be more specific on the problem, but by not working, I
> mean not working:-) There is nothing in any textbox using the Date()
> VBA function when there should be (used to be before the switch)
> today's date.
> I have a couple of ideas to check into tomorrow, but any help you could
> give would be appreciated.
> Thanks
> Chris
> Tibor Karaszi wrote:
>> It is hard to debug "not working". Possibly that function result in date being passed as a string
>> to
>> SQL Server, and different string formats being messed up due to language settings. Use Profiler
>> to
>> see what is really submitted to SQL Server. Also see
>> http://www.karaszi.com/SQLServer/info_datetime.asp
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> <creejohnson@.gmail.com> wrote in message
>> news:1159308950.973840.54110@.i3g2000cwc.googlegroups.com...
>> > Hi--
>> >
>> > We just upgraded our server and now have Sql 2005 as a backend to our
>> > Access ADP. With SQL 2000, we had used functions like Date() to provide
>> > default values for certain fields. Now, these functions are not working
>> > on the clients. However, they do work on every computer with SQL 2005
>> > installed. I have 2005 installed on my laptop and the server and am
>> > having no problems on those computers. Is there anything I can do to
>> > the clients to resolve this issue' Thanks in advance.
>> >
>> > Chris
>> >
>