I'm using this script I found on the web. After some tweaking here and there, it works great. I'd like to change it to pull data from a SQL source instead of Access. Anyone know how to do this, give me some pointers?
Here is the script
*****************************************************************************************************************
<%@ LANGUAGE="VBSCRIPT" %>
<% Option Explicit %>
<%
Dim DATA_PATH, Conn, DataRecords, email, user, pass, sendmail
'Maps to database. Change to your database path.
DATA_PATH=Server.Mappath("membersdb.mdb")
' Create and intiate data connection
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.ConnectionTimeout = 15
Conn.CommandTimeout = 30
Conn.Open "DBQ=" & DATA_PATH & ";Driver={Microsoft Access Driver (*.mdb)}; DriverId=25;MaxBufferSize=8192;Threads=20;", "admin", "password"
Set DataRecords = Server.CreateObject("ADODB.Recordset")
email=request.form("email")
'The magic query to look for registered members in the database
DataRecords.Open "SELECT * FROM MEMBERS WHERE email = '" & email & "'", Conn, 0, 1
%>
<%
'checks if email address exists in the database before sending a message.
if DataRecords.EOF then
%>
We could not find <%=email%> in our database.
<% Else %>
<%
'sets variables
email = request.form("email")
'chooses username and password from database that correspond to submitted email address.
user = DataRecords.Fields("usernames")
pass = DataRecords.Fields("password")
Set sendmail = Server.CreateObject("CDONTS.NewMail")
'put the webmaster address here
sendmail.From = "Someone@somewhere.com"
'The mail is sent to the address entered in the previous page.
sendmail.To = email
'Enter the subject of your mail here
sendmail.Subject = "Membership Login Information"
'This is the content of thr message.
sendmail.Body = "Per your request your account login information is: " & vbCrlf & vbCrlf _
& "Username=" & user & vbCrlf _
& "Password=" & pass & vbCrlf
'this sets mail priority.... 0=low 1=normal 2=high
'sendmail.Importance = 3 commented out causes a error for a call or procedure (line 41)
sendmail.Send
%>
We just sent your login information to <%=email%>.
You should receive it shortly.
<%
' Close Data Access Objects and free connection variables
Conn.Close
Set DataRecords = Nothing
Set Conn = Nothing
Set sendmail = Nothing
%><%end if%>
***************************************************************************************************************
And here's my SQL connection stuff
Private Sub Class_Initialize()
ConnectionString = "Provider=SQLNCLI.1;Persist Security Info=False;User ID=sa;Initial Catalog=ABC;Data Source=xx.xx.xx.xx"
User = "usr"
Password = "pswrd"
Set Converter = New clsConverter
Converter.DateFormat = Array("yyyy", "-", "mm", "-", "dd", " ", "HH", ":", "nn", ":", "ss")
Converter.BooleanFormat = Array(1, 0, Empty)
Set objConnection = Server.CreateObject("ADODB.Connection")
Database = "MSSQLServer"
Set Errors = New clsErrors
End Sub
****************************************************************************************************************
I am having issues with MSAccess and MS SQL for a new client. It worked in the past, but I cannot talk to the old computer guy?!# Here's the scoop. It uses an Access front end and ODBC to conect to the MS SQL 7.0 server. In the office they use a system DSN named DATA to connect using NT authentication. I've got the firewall configured for the two employees static ip addresses to goto the MS SQL server. On the remote machines, I have added a system DNS named DATA as well that uses MS SQL authentication. I setup the remote user accounts on the MS SQL server. Now heres the problem. I can test the ODBC from the remote omputer and it works.At first I could not get into teh database at all. I went in and updated the Access file connection strings to include a username and password. Now I can get into the Access front end and access the main page. (IT has a couple of buttons - Shpping, Inventory, Management, etc.) I can even click on these buttons and pull up the next page. (For example I can click on the management button and pull up a list of new buttons- one of which is Auto Exec Bob) NOw when I click autoexec bob or any of the others it takes a while and then give me an error. (SQLState 28000 MS SQL error: 18452 Login failed for user null. Reason: Not associated with a trusted SQL Server Connection.) If I click ok then I have a box that lets me uncheck the use trusted connection and sign in using my remote username and pass that I setup. Eventually I can access everything - one step at a time.Each time it tries to connect to the database it takes about thirty seconds to time out, then gives me this error message and I can log on again. Any help to sort this out would be greatly appreciated. Thanks in advance.
I have MS SQL Server 2000 evaluation version on my desktop PC. I havethe SQL Server client tools (only) on the laptop. The two computers arenetworked. I had an Access MDB database on my laptop, but I convertedit with the Access upsizing wizard to an Access ADP database with theAccess front-end on the laptop connecting to the SQL Server on thedesktop. Now I want to move from this test environment to the "real"server. How do I go about this? Do I make the change from the laptop orthe desktop? Do I use the Access drop-down menu, Tools > DatabaseUtilities > Transfer Database? Or maybe from the desktop with: SQLServer Enterprise Manager > Copy Database Wizard?
I have a database with a table of 1,123 records that I need to change in a hurry! The problem is I do not know how to do it.... For example there are 2 fields that are numerical and formatted buy currency 1 record reads 400 and the other reads 400, but I need to double the amount for both fields and all of the records. Please help..... Thanx in advance for any and all help..
I designed a database that works well in Access and I want to get it to run in SQL Server. How do I learn how and what to do to change over to SQL Server?
I designed a database that works well in Access and I want to get it to run in SQL Server. How do I learn how and what to do to change over to SQL Server?
I cannot open now my database after changing it name from the folder where it is save and also changed back to its original name.Message recovery pending now is appearing as shown below.Any chance that I could recover my database back?
Client/Server machine: Windows Xp Pro (SP2) (latest patches) Office Software: Access 2003 (latest patches) Database S/W: SQL Server 2005 (latest patches)
The following error message is displayed when trying to modify a stored procedure.
This version of Microsoft Access doesn't support design changes to the version of Microsoft SQL Server your project is connected to. See the Microsoft Office Update Web site for the latest information and downloads (on the Help menu, click Office on the Web). Your design changes will not be saved.
However, if you save, close and re-open the stored procedure having made the required changes, the changes have been saved.
Is there any way to suppress the error message / hotfix available from microsoft since the error message appears to be completely erroneous ?
Have I provided enough detail as this is my first post ?
I'm using MSAccess 2k as a front end to a SQL Server 2k database.
I have a user set up in SQL Server to be database owner for a specific database.
Using NT authentication.
Using ADO.
The MSAccess application executes a store procedure on the SQL Server that (1) deletes a tables if it exists, (2) creates and populates a table (the owner shows up in Enterprise Manager as the user name) and then (3) uses the table (read) in a join to do some other processing.
The (1) delete works fine. The (2) table is created and populated fine. On (3) the Select (inner join) to do the aditional processing I get an error message that the table name used is an invalid object.
Workaround. If I create the table as sa separately and in the store procedure, instead of deleting, creating and populating the table I merely truncate the table and then repopulate it, everything works fine.
I would prefer to not have the table sitting around the database all the time.
Anyway, I'd like to figure out why the table is causing a problem.
I´m having a problem setting up a job in sql. When i run my DTS is on succed but when I schedule the job --failed Is connecting a sql serve db with msaccess you.
I am getting below error when exporting data from SQL Server 2005 to MS Access.
[DTS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Destination" (1706) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
Its direct mapping from OLE DB Source (SQL Server 2005) to OLE DB Dest (MS-Access Database)
I would like to pull some data from a SQLServer database, and save it into an Access MDB file (which can be empty to start). I would then zip up the MDB and download it to the user.
I am seeking advice on the most "elegant" or "efficient" way to do this. Here are some ideas I have been considering:
1) Should I start with an empty template MDB and file-copy it before I populate it? Or is there a neat way in ASP.NET to allocate a brand new MDB outright?
2) I could read the SQLServer data into a Dataset object. I could then open a connection to the MDB, create a table object, defining all the columns, etc., and then I could write the data to the new table object. BUT ... I have a hunch there is a nifty ADO.NET way to save the data already in the Dataset object right into the MDB (creating the table and columns as a matter of course) ... all with an instruction or two (or three). Any ideas?
We have a MSSQL 2000 db, with several MSAccess 2000 adp interfaces connected to it.
Several bit data type fields of the tables are updated through those interfaces, but from time to time (I can find no pattern when it happens), flagged fields (i.e. with value 1) are unflagged (they get value 0), without any user interaction on that field (neither in the tables, neither through the checkboxes).
SELECT Hist_POS.POSITION_CLASS_CODE, First(Hist_POS.POSITION_TITLE) AS FirstOfPOSITION_TITLE INTO [Manhour Positions] FROM Hist_POS GROUP BY Hist_POS.POSITION_CLASS_CODE ORDER BY Hist_POS.POSITION_CLASS_CODE;
But not SQLServer because of First(). How can I achieve the same results in SQLServer?
Hitaishi writes "We have recently migrated from MSAccess as our back end system to SQL Express, but the dialogs showing records of the table which were taking lesser time in Access are taking more time in opening and doing any database operations.......
We are using .Net Framework 2.0. Does it have anything to do with lowering down the performance ?
I am trying to use SQLServer for the first time and I am trying to use the upsizing wizard on Access to get moved over to SQLServer. I am getting the following error messages:
im in the begginging stages of converting an access 2003 backend to SQL 2003sp3.
The access's upgrade wizzard did an ok job getting most of the tables ported over. Now when the frontend relinked the tables to SQL server im assuming i just continue on using them as is. When the wizzard wanted a connection to use i created a new connection and saved it on the server. Im assuming this connection object is what access is using to get to SQL.
Am i assuming correctly ?
The reason i ask is only a few of the linked tables in to sql seem to be editable. some are read only ? (i have primary keys in all my tables.) im just calling a query bound to the linked tables.
Secondly, But could be related.
Now this problem i dont understand. On open of my main form i log who and what pc they are using. in this situation i open a record set, log the data and close it. At first the error was i needed to add dbSeeChanges on my object, ok so i added it as an option.
But i now get an the error message: at rsTran.AddNew "Run Time error: 3027 , Can Not update. Database or Object is read Only ?
here is the code
Set db = CurrentDb Set rsTran = db.OpenRecordset("tblUserLog", dbOpenDynaset, dbSeeChanges) struser = Currentuser strNetwork = atCNames(1) strPC = atCNames(2)
We have plan to migrate our database from Access to SqlCe.
Here our situation:
We will never use a Pocket PC applications, only Windows desktop application. We want to migrate the .mdb file to a .sdf file (I've seen the ADS application, but since we don't use mobile devices, it's no use) The .sdf will be use on local client machine only. Here's my problems:
I've use a conversion to migrate from access (http://www.primeworks-mobile.com/Downloads/DPW.html) and seem to work fine. I updated the source code to use the SqlServerCe dll instead of OleDB. Some of my queries work fine, but others just won't work in VS2005 using C#.
To check if the query is really wrong, I take exactly the same query and put it Query Builder from the Server Explorer in VS2005. And the query just work fines!!! It's bring back all the records asked for, and I've got an error for the same query from my source code.
I'm using a datatable, here's the code used for filling the DataTable. I've got an error on the line adapter.FillSchema()
string connectionString = @"Data Source=" + fic_parent.CheminFichierComplet; using (SqlCeConnection connection = new SqlCeConnection(connectionString)) { SqlCeDataAdapter adapter = new SqlCeDataAdapter();
connection.Open();
SqlCeCommand command = new SqlCeCommand(requete, connection);
tableResultats = new DataTable(); adapter.FillSchema(tableResultats, SchemaType.Source); adapter.Fill(tableResultats);
// Close connection connection.Close(); }
Error is:
Message="The specified table does not exist. [ (...) ]" Source="SQL Server 2005 Everywhere Edition ADO.NET Data Provider" HResult=-2147217865 NativeError=0
Here my questions:
Is it possible to have an application that will convert my Access databases to an SSCe .sdf file ? Why I can query in Server Explorer and can't in my source code using the same sql statement ?
At which level my code isn't good, because I just replace all OleDB* --> SqlCe* in my code. I hope that I explain clearly my problem, do not hesitate to ask me questions about this problem.
I have a Access 2002 (saved as 2K format) front end to a SQL Server (2000 I believe) and everything worked fine until I added 30 new fields to the main table. All of these fields are data type Bit and all are initialized to zero. I can add new records without any issues. The problem is that any time I try to update or delete a record I get the dreaded "Write Conflict..." msg in Access. If I use an older version of the system without refreshing the linked tables I have no problems. This behavior is also exhibited if I try to update/delete a record directly in the table so I know it is not something in my forms. Any help would be greatly appreciated.
I need to switch from MS Access to SQL Server for my database. To set up a development environment I downloaded the free Microsoft SQL Server Express (February CTP version). I installed the required .NET Framework v2, and then SQLExpress. The install was done using all the defaults, and was done successfully. I also downloaded and installed the SQL Express Manager Tool.
The SQL Server was installed on the same machine as my VS.NET development environment. The SQL Server process is now running, and I can connect to the server using the SQL Express Manager Tool. This allows me to view and query the sample databases, but not much else.
To convert my Access .MDB database to SQL Server, I am trying to use the MS Access Upsizing Wizard. The version of Access I am using is Access 2002 on a Windows XP-Professional system. The problem is that Access cannot get a connection to the SQL Server. I tried using the default server name "(local)" and "Use Trusted Connection", but I receive the following error:
Connection failed: SQLState: '01000' SQL Server Error: 2 [Microsoft][ODBC SQL Server Driver[]Shared Memory]ConnectionOpen (Connect()). Connection failed: SQLState: '08001' SQL Server Error: 17 [Microsoft][ODBC SQL Server Driver[]Shared Memory]SQL Server does not exist or access denied.
Curiously, I get this exact same error message even if the SQL service is stopped. So I'm pretty sure the problem is that it is not finding the SQL server, and not a security issue.
In order to connect to the SQL server using the SQL Server Manager Tool, you have to provide the actual instance name for the server "COMPNAMESQLExpress". So I tried using this server name in the Access Upsizing Wizard, but this returns the same error message as above except the first SQL Server Error is 53. I also tried using a Login ID and password (using the Windows administrator ID and password, and also the "sa" ID and password) to no avail.
I am at wits end, and can't figure out why Access can't find the SQL Server. Any ideas would be appreciated.
Hi,I am very, very new to SQL, so be patient.On a W2K3-server runs SQL2000 (Sp3a). I have a database in MSAccess (2003) and want to importthe data with the wizard. All went well and I got the whole database in SQL.Now I am back in VS.net and want to create a SQLAdapter and when doing that I got the warning:UPDATE and DELETE statement. Could not determin which columns uniquely identify the rows for "<Table>".The structure of "<Table>" in MSAccess was:Id (auto) as primary keyField1 (text,50)Field2 (text,50)That's all.The structure of the SQL-table after importing-Wizard:Id (int, Not Null)Field1 (nvarchar(50),Null)Field2 (nvarchar(50),Null)Why cannot SQL recognise this primary key ?Regards, Ger.
Hey guys. I have a SQL query I'm trying to create. It's nice and dandy, but I have a memo field that's being trunicated to 256 characters.
SQL is something like this:
Code:
Select distinct `group`,sortorder,HideLabel,category,img,Description,Null as num,Null as subcat from catalogimages where Inact=0 and subcateg is NULL group by `group`,sortorder,HideLabel,category,img,Description order by `group`,sortorder,category
Description would be the memo field.
Is the 'GROUP BY' clause even necessary here? I'm also willing to bet that the 'DISTINCT' clause might not be necessary. Any help would be greatly appreciated.
hi, i export datas from sql server 2000 to Ms Access. but few tables only not expot. and error has come "the microsoft jet data base engine cannot find the input or Query "<t.n>" make sure it exists and that its name is spelled correctly.
I have an SSIS package that takes data from a table in Access and puts it into a fact table in SS2005. Very little data manipulation is done. It processes approximately 1.5 million rows when it runs weekly. The process is run in an SSIS package that is called by a parent package, and all of that (including the use of the config files and accessing the parent variables) is working fine.
The issue is there is one field in the Access table that must be put into a different SS2005 fact table.
When I run the data flow task that loads the first fact table, it completes in less than two minutes. However, if I either (a) put a multicast step in the dataflow task to redirect a copy of the key data and remaining field to the second fact table, or (b) copy that step in the package to have it perform the same tasks with the different target (and using just the key and the remaining field), the execution time suddenly jumps to 30 minutes. In the case of (b), it remains true whether the copied step remains in the package or is executed in its own package, and also remains true if the package is loading against a table that starts out empty or with data already in it.
Has anyone ever bumped into a situation like this?
This started as a simple thing, allow somebody to access to a database on a SQL 2005 server so they could get to it from MSAccess. I have created a login (Windows Authentication) so they can see the database and gave them "public" and "datareader" abilities.
The user then informed me that while they could see the views they could not see the data. Not being a DBA (sorry) I added "public" to the view's permissions (under properties) and granted it "Select" access and nothing else. After I completed this the user could not even see the views to pick them (via the ODBC link from MSAccess) and when I try to remove "public" from the permissions it dissapears, but when I reopen the permission "public" is back.
I then find out there is no data in the views and so the user would not have been able view anything anyway.
So what I want to do is remove "public" from the views permissions, is this possible?
I cannot get a simple package to execute a data pump to an access database from SQL2005. I have tried it in both SSIS and by running the Export data function. I have been able to write to this database in the past using dtp in SQL2000 but I am not able to write to it using SQL2005. What is the deal with the new SSIS? Does anybody have any ideas I can try to get my export to work. I have many more to do and I have to migrate over all of my SQL 2000 DTS packages to SQL2005 and some export to MSAccess.
This is the only error message I can find: [DTS.Pipeline] Information: "component "OLE DB Destination 1" (2196)" wrote 0 rows.
Edit: I found more errors in the debug section and a post here that discussed the problem as they had run into it. I was able to use part of that and some more research in order to tackle my problem.
I would still be interested in finding out why I suddenly had this problem arise after I upgraded to SQL2005. This is going to be a real pain as apparently SQL2005 treats NULL as Zero Length and now all of my databsaes that had that set in access will have to be modified to deal with this in the export.
I have one UDF which take 5 params. When I run this UDF from Query Analyzer (select * from dbo.UDFname (param1, ..., param5)), it take 5 seconds, but when I run from ADP project (doble click), it take > 200 seconds. In both situations I obtain the same result (of course). I look at the connexion, seems ok (File -> Connexion; in .adp).
Can anybody tell me why are this differences? And how to surpass this ? I want to use the access .adp to make one front to run a MSDE db.