SQL Server

Nov 30, 2006

Using A2002 or A2003, trying to convert an app from Jet to SqlServer



i am using DAO rather than ADO

this statement causes an error
Set rs = dbs.OpenRecordset("tblconstants", , dbSeeChanges)

the error is
error 3622 - must use dbseechanges on a sql server table with identitiy bit set


alternatively
Set rs = dbs.OpenRecordset("tblconstants", dbOpenDynamic)

produces error 3001 - invalid argument


(Note: i've got another app, in A97 that works with sql server tables without any additional parameters )

Set rs = dbs.OpenRecordset("tblconstants") works in A97

------------------
any idea what do i need to do please

View Replies


ADVERTISEMENT

One .adp, Two Or More Different SQL Server

Feb 18, 2007

Hi. hope you can understand my english.

I have 2 SQL Servers.
Is there some way to set to which sql server connect the .adp (or .ade) via code??
Now I have 2 sql servers (development and production).. but soon I will have more server at production in different cities. All SQL Servers have different names. Same databases names but the servername are all different.
So I have to configure the connection of the .ade in the the development server. Then when I want to send and upgrade to one of my clients, I have to go to his city and manually reconfigure the .ade to connect to his SQL Server.
This problem would be solved if all SQL Servers had the same name, but this is not my case.

It would be nice to have a config.ini file or something like that where the .ade could read the name of the server to connect to.

Is it possible to implement something like this.

Thanks in advance.

View 1 Replies View Related

SQL Server - Yes Or No

Sep 6, 2004

Hi, i am looking at making use of SQL Server which we have at work to run our access Database from, is this a good idea ?

what are the benifits, problems etc...

Andy

View 1 Replies View Related

OLE Server

Nov 14, 2004

I am trying to insert a picture in a field OLE object, but after I have selected the picture I would like to insert, I get the following error message :
A problem occured while MS Access was communicating with the OLE server or ActiveX Control.
Can someone help me to resolve this problem ?

View 1 Replies View Related

SQL Server

Apr 19, 2005

I've never dealt with SQL Server, and have no working knowledge of it. Is it possible to upsize my db without it? The server that we have uses Windows 2000 Server as it's OS, would this be capable of housing an upsized db? I was tols that MSDE is only capable of about 15 concurrent users, and I will be having +-100 concurrent users on this particular db. Is there another option?

View 1 Replies View Related

From Jet SQL To SQL Server

Mar 25, 2007

Hi guys, its been awhile. I inherited a database thats an MDB and I need to get it on to sql server but I am having problem with this query, can anyone help please. Its suppose to give you the month and year when you click on it, you just put that in and up come the results on a query or report, now I have something similar to this but the code is very different.


SELECT DISTINCTROW Format$([Main Table].Date,'yyyy') AS [Date By Year], [Main Table].[Violation Type], Sum([Main Table].Loss) AS [Sum Of Loss], Count([Main Table].[Violation Type]) AS [CountOfViolation Type]
FROM [Main Table]
GROUP BY Format$([Main Table].Date,'yyyy'), [Main Table].[Violation Type], Year([Main Table].Date)
HAVING (((Year([Main Table].[Date]))=[Please Enter the Year]));

This gives them a dialog box which ask them for month and year and they enter it in and they get a generated report or form which gives them their results. But when I transfer this to SQL it gives me error message cause Jet SQL and SQL Server dont exatly speak the same language, I mean SQL server doesnt know what Format$ is I guess thats something ACCess comes up with on its own or something, I dont know. HELP PLEASE!!??

View 2 Replies View Related

SQL Server... Where Do I Start?

Jul 20, 2005

I've downloaded and installed the new SQL server 2005 express edition CTD (June ed.) along with the Express Manager.

How do i get an access database into SQL server?

Now that it's installed, what next? When i try to upsize, it asks me to choose a server, and gives 3 options, and i'm not sure where they come from. Where does this list come from? are they previous installations of the MSDE on my computer? are they servers that ar available over our LAN?

i read that the default name if an instance of SQL server express is SQLEXPRESS but that's not on the list...

do i need to create a new instance of SQL server on my computer to house the database?

I really just want to try it, to see if it's something we should do, but i can't get it to do anything at all. i don't know where to start...

I've done some searches, but nothing tells me where to start. I'm getting really frustrated with this....

Please help me...

View 2 Replies View Related

Getting Server/PC Time

Jan 31, 2006

Hi,
This is the first tim here, hope to find what I am looking for :D

I am developing a small form in MS Access for recording attendence. I made a form and a buton which get the time and store it in a table.

The problem happens when a user change the his computer time and click the button, the new time will be recorded.

The question is how to fix the time or get the time from a centeral computer or server?

Note the DB will be placed on a shared PC or on a server in the LAN.

Thanks,

View 2 Replies View Related

SQL Server Tables

Mar 16, 2006

I have A db that links to a SQL Server table via an odbc connection. The problem is that I have to set up the odbc datasource on each machine that needs to run the db. Can I set up an ADO connection to the table and use that connection as a record source through out the app to drive forms , reports, etc? (And this would eliminate the need to set up and odbc connection to the table on each machine...) :confused: :confused:

View 6 Replies View Related

Oracle, DB2 Or SQL Server?

Apr 19, 2006

Oracle Express Edition, DB2 Express Edition-C, and SQL Server Express Edition.Which one would you develop for / work with if you had a choice (SQL Server, Oracle, or DB2)?I am thinking of making a database application with one of the above and the criteria is:Great support, communityRock Solid and stable; where some clients do not have a DBAFastEasy to back-up and restoreRelatively easy to program and developEasy to deploy and scaleThe general direction I am getting from a few people is leaning toward SQL Server Express. They say it is easier to work with than Oracle, (supposedly Oracle is not for the faint hearted especially when there are network issues) but Oracle is faster and maybe more stable than the others. Haven't really heard anything about DB2...MySQL is out, simply because of the price of an OEM for a desktop application that can be expanded later is too high for what I want to do.Thoughts?Thanks

View 1 Replies View Related

Help! Transferring To New Server

Oct 11, 2006

I've been asked to assist a local non-profit transfer an Access DB to a new server. The DB was set up a number of years ago and the designers are no longer available.

The old server is set up as a workgroup server, and the new server is set up as a domain server. (I am completely unfamiliar with how networks are set up...I've never dealt with this end before.)

The designers set up a .mdw file which (I believe) controls permissions etc. After much wrangling, we finally uncovered the admin password. However, even signed in on a workstation as the admin w/ full rights, I still can't copy a functional version of the DB to removable media in order to move it to the new server.

The server does not have MS Access loaded....only the .mdb and .mdw files.

I *think* I have to replace the 'secured' .mdw file with a generic, clean .mdw. Do I need to do this on the server, or the workstations, or both?

What am I doing wrong????

Thanks!

View 1 Replies View Related

SQL Server Connection?

Jan 30, 2007

Hi all,

I have an .adp that works fine on a number of people's laptops.

One of the users however, keeps getting an error message stating that SQL Server does not exist or access has been denied.

This particular user is definitely setup on SQL Server with dbo permissions. She can also ping the server.

I have tried creating a DSN and this doesn't work either.

I am at a loss as to why this error message is occurring given that she can see the Server and has definitely been setup.

Do you have any suggestions?

Thanks,
Kabir

View 1 Replies View Related

Move To SQL Server

Apr 20, 2007

I'm planning make a move from Access to SQL Server.
To be honest I never used it before, but I told to my bosses that I'm a Gooroo in it.:)
So can you guys give me an idea if it is really hard to make a move, to upload the existing databases and to get used to it.
How long it takes?
What books would you recommend?
Or what forum?

View 10 Replies View Related

SMS Messaging Server

Jun 11, 2007

Dear All,

I have developed a full attendance and payroll system.
I want to make this system available for responding remotely as well through email and SMS.

Thus, I am planning to attach a GSM modem into the server hosting the system, and assigning a fixed telephone no. The client can send SMS to this server, which in turn respond to his request.
To make this, I should also install a third party software through which I can access to the Incoming and outgoing SMSs database using MS Access coding.

I have got such third party software called Activexperts SMS Messaging Server. However, the software license is $ 1,400 which is expensive for me.

Thus, can you suggest me another software name I can use to handle such job.

Thanks,

View 4 Replies View Related

Client/Server?

Jun 18, 2007

Hello, I am new to this forum and hope someone will be able to help me.

I understand that when querying using a split MS Access database it retrieves all the data to the client machine and then applies the criteria (hope this is correct) at the client side.

If I were to link to Oracle XE tables via ODBC, does a query using these linked tables still bring across all the records to the client or will it run the query on the server and just bring down the records matching that criteria?

Many thanks in advance...

View 5 Replies View Related

ADP Or MDB Linked To Server

Nov 2, 2007

Is it same thing about speed and other issues to have ADP application that works with SQLserver and to have a MDB (or MDE) application that has no querys but works with sql views instead?

Thanks

View 1 Replies View Related

Access FE SQL Server BE

Dec 5, 2007

We're currently looking at changing a couple of my more complex Access 2003 databases to Access Front End with a SQL Server Back End. This should give us better stability and reduce network traffic loads. I was looking at the Access help file on Upsizing and came across the following paragraph. On face value to a novice like me this would seem the easy option - but is it?

"Create an Access database front-end to an SQL Server database back-end so that you can create a front-end/back-end application.

This approach requires very little application modification since the code is still using the Jet database engine. "

Would anyone recommend this approach?

View 4 Replies View Related

Ole Server Problem

Feb 13, 2006

I have an Access 2000 database that is linked to a SQL server back end. One of the tables has 4 OLE object fields into which we insert bitmaps of the client's logo. We do this by right-clicking on the field and then going to Insert Object > Create from File and then select the .bmp file to be inserted. This works fine when I do it on my site, but when I connect to the customer's machine using PC Anywhere and try to do it on his machine, I get a message saying that the OLE Server isn't registered.

I've searched on the error message and all the answers say that they apply to Access 2002 and 2003.

What might be causing it in Access 2000 and how do I fix it?

View 1 Replies View Related

Connecting To MS-SQL Server

Mar 25, 2006

Hi
We have a database implemented in MS-SQL server 2000 on a local machine. I want to use some of tables in my access (or excel) program. Can I link to the table?

Thanks

View 1 Replies View Related

Linking To SQL Server

Sep 20, 2006

Strewth didn't think I was ever going to work with Access again, but I am, so here comes a table link question for you.

Got an access xp FE linking to SQL server via a DSN file. All linked up fine, however the tables in access have been given a 'dbo_' prefix to the original table name in SQL server. :rolleyes:

How to get rid off?

A setting in the DSN file?
Some VBA to loop through each table and rename?
Linked table manager which I haven't got round to installing yet?

Thanks

Dan

View 2 Replies View Related

First Function In SQL Server

May 19, 2005

Hi All,

I m using MS-access as backend and I am using the following query where I use FIRST() as aggregrate function.

SELECT FIRST(COUNTRY) AS FIRSTOFCOUNTRY, ZONECODE FROM LOCATIONS GROUP BY COUNTRY,ZONECODE
HAVING ZONECODE = "ZONE01"

Now I have to shift my access database to SQL server 2000 where First() function does not work. I had searched for that but still cant find the alternative.

Can any one help me in solving this problem? :(

Thanks in advance,

Regards,

Jigs

View 3 Replies View Related

SQL Server To Access

Feb 15, 2006

I am importing an Excel file into a table in SQL Server that was created by an AS400 program.

I linked this table in MS Access. The date fields from the SQL Server table are split out. DSREQM is the month and day. DSREQY is the year. When opening the table it looks like this:

DSREQM DSREQY
215 6

I need to be able to convert this is a field to 2/15/06. However I cannot get this to work. For some reason when I execute the query it comes out as 8/02. Here is what I have:

DelDate: Format([DSREQM],"mdd") & "/" & Format([DSREQY],"y")

Any ideas?

View 5 Replies View Related

Putting An .mdb On A Server

Nov 7, 2004

soooo now my client wants the program i just wrote on his brand new server that he doesn't know how to use. i've semi-split my db (i kept my lookup tables with the program to save room for data). i'm not very familiar w/networking. my assumption is that i just put the BE on the server and put the FE on their machines, but i was wondering if i could put both the FE and the BE on the server with a shortcut to the FE so that i could update it and not have to worry about making sure everyone has the most recent copy.

anyone have any suggestions on how to make this run smoothly and make it appear as though i have some semblance of what i'm supposed to be doing?

-Jason

View 14 Replies View Related

Accessing SQL Server

Feb 25, 2005

I have time entry program that the company I work for recently purchased that uses a SQL server 2000 Back end. I can connect to the database just fine if I create a project in Access. What I need to do is take data out of a couple of the tables in the SQL Server and place them in an access table. I really don't want to add any code or tables to the Access project that I created since it seems it would add tables and queries to this products database. What would be the best way to siphon data out of this software package for my own use? This would become a daily process so it needs to be fairly automated and easy.

Thank you for your help.

View 2 Replies View Related

Insertion In To SQL Server

Sep 12, 2006

I have an Access front end to a table residing on an SQL server. I am accessing the SQL server through a VPN over a DSL line.

Now, I have some data I need to insert in to that table. It sits in a text file. All in all, there are around 9000 records in the text file.

I had excel reading the text file, then cleaning up the data, then I would link the excel table via ODBC, and link to the SQL Server table, then do an INSERT INTO ServerTable Select * from ExcelTable; That would take about 22 minutes to finish.

I cleaned things up a bit, using VB to directly read the text file, and I can insert in to the ServerTable directly via an ADO recordset in about 18 minutes.

I want to get the time down further.

Now, if I read that text file and use ADO, I can insert all the records in an Access table on network share across that same VPN in about 4 minutes, so I figure there is about 14 minutes of overhead in dealing with the SQL server.

So is there anything I can do to decrease the amount of time it takes to insert the 9000 records in to the ServerTable?

Thanks,
David

PS. Oddly, I tried to limit the number of fields I data I upload, and found it actually took MORE time to send one field than it did to send all 30 some fields in the full dataset. I really can't figure that out.

View 2 Replies View Related

Access Using Jet And SQL Server With ADO

Aug 29, 2007

Hi

I am attempting to write an application, using Access2003 as the front end. The Backend May be either Jet (ie an MDB), or SQL Server.

I am trying to make it so that the same code works regardless of the back end.

My approach has been to set the forms recordset to an ADO recordset like so:

Set Rs = New ADODB.Recordset
'Rs.CursorLocation = adUseClient
Rs.CursorLocation = adUseServer
Rs.Open strSQL, gcn, adOpenDynamic, adLockOptimistic
'
Set Me.Recordset = Rs

Anyone done this, and willing to sahre information/pitfalls.

First problem I am encountering is that Jet only seems to like .CursorLocation = adUseClient, and SQL Server only seemns to like .CursorLocation = adUseServer

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved