Dear All,
I would like to convert from Access To SQL DB undervisual Studio.Net 2005...
How can I do it easily, or if there any software to do this automaticlly, please your help..
Awaiting your valuable reply.
Many thanks in advance for your cooperation and continuous support....
The following is my code for Access... can someone help me convert it to sql:
My Connectionstring is "server=(local);database=Database;trusted_connection=true"
<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script language="VB" runat="server">
Sub btnLogin_OnClick(Src As Object, E As EventArgs)
Dim myConnection As OleDbConnection
Dim myCommand As OleDbCommand
Dim intUserCount As Integer
Dim strSQL As String
strSQL = "SELECT COUNT(*) FROM tblLoginInfo " _
& "WHERE username='" & Replace(txtUsername.Text, "'", "''") & "' " _
& "AND password='" & Replace(txtPassword.Text, "'", "''") & "';"
myConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " _
& "Data Source=" & Server.MapPath("login.mdb") & ";")
myCommand = New OleDbCommand(strSQL, myConnection)
myConnection.Open()
intUserCount = myCommand.ExecuteScalar()
myConnection.Close()
If intUserCount > 0 Then
lblInvalid.Text = ""
FormsAuthentication.SetAuthCookie(txtUsername.Text, True)
Response.Redirect("login_db-protected.aspx")
Else
lblInvalid.Text = "Sorry... try again..."
End If
End Sub
</script>
I am currently in the process of writing an application to convert an Access database to SQL. Basically, I have created an odbc link in access and then I transfer the data from the access table to the linked table.
The problem is where I have to transfer the Identity keys. I can transfer all the data but the keys. They will auto increment. I tried using INSERT_IDENTITY tablename ON but it just returns an error mosty of the time. I have gotten it to transfer once or twice by stepping through.
Here is the basic code:
The code that causes everything to error is commented out.
Private Sub CopySQLRecordSet(ByVal stTableName As String, _
Optional ByVal blIdentity As Boolean = True, _
Optional ByVal stSearch As String = "")
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Set cat = New ADOX.Catalog
Set tbl = New ADOX.Table
Dim adoCommand As New ADODB.Command, stCommand As String
Dim adoSQLCommand As New ADODB.Command
Dim errorString As String
On Error GoTo ErrorCopying
PrgPart.Value = PrgPart.Value + 1
If blCancelPressed Then End '???fix later
cat.ActiveConnection = db1 'This doesn't seem to work with our normal settings for spectrumDbase
tbl.ParentCatalog = cat
tbl.Name = "dbo_" & stTableName
tbl.Properties("Temporary Table") = False
'possibly, this line will work for Oracle as well
tbl.Properties("Jet OLEDB:Link Provider String") = "odbc;DSN=
;DATABASE=database;"
tbl.Properties("Jet OLEDB:Remote Table Name") = stTableName
tbl.Properties("Jet OLEDB:Create Link") = True
tbl.Properties("Jet OLEDB:Table Hidden In Access") = False
tbl.Properties("Jet OLEDB:Cache Link Name/Password") = False
cat.Tables.Append tbl
Suspend 1
adoCommand.CommandType = adCmdText
adoCommand.ActiveConnection = database
adoSQLCommand.CommandType = adCmdText
adoSQLCommand.ActiveConnection = rsDbase
'spectrumDBase.BeginTrans
'stCommand = "BEGIN TRANSACTION " & vbNewLine _
' & "go" & vbNewLine
'If there is an identity field in the table, it must be temporarily disabled
'to insert from an foreign DB.
'If blIdentity Then
' stCommand = stCommand & "SET IDENTITY_INSERT dbo_" & stTableName & " ON " & vbNewLine & "GO" & vbNewLine
'adoCommand.CommandText = stCommand
'adoCommand.Execute
'End If
'insert the records from the source table
stCommand = ""
stCommand = stCommand & "INSERT INTO dbo_" & stTableName _
& " SELECT * FROM " & stTableName & vbNewLine _
& "GO" & vbNewLine
'stCommand = stCommand & "COMMIT TRANSACTION"
adoCommand.CommandText = stCommand
adoCommand.Execute
'clean up
' stCommand = stCommand & vbNewLine & "DROP TABLE dbo_" & stTableName & vbNewLine & "GO"
' adoCommand.CommandText = stCommand
' adoCommand.Execute
'reinsert identity property
' If blIdentity Then stCommand = "SET IDENTITY_INSERT " & stTableName & " OFF" & vbNewLine
'Block statement, going to leave this out for now
'stCommand = stCommand & "go" & vbNewLine _
'& "COMMIT TRANSACTION"
'adoCommand.CommandText = stCommand
'adoCommand.Execute
Set adoCommand = Nothing
Set adoSQLCommand = Nothing
Exit Sub
ErrorCopying:
'need to save this to a string so it doesn't reset when Resume occurs
errorString = Err.Description
' MsgBox "Error copying the [" & stTableName & "] table." & vbNewLine _
' & "Error: " & errorstring
Resume errorCatch
errorCatch:
On Error Resume Next
'set a log
Dim fso As FileSystemObject, fStream As TextStream
Set fso = New FileSystemObject
Set fStream = fso.OpenTextFile(App.Path & "DBTransfer.log", ForAppending, True)
fStream.WriteLine "***Error copying the [" & stTableName & "] table."
fStream.WriteLine " Error: " & errorString
fStream.WriteLine " "
fStream.Close
Set fStream = Nothing
Set fso = Nothing
'clean up
adoCommand.CommandText = "DROP TABLE dbo_" & stTableName
adoCommand.Execute
If blIdentity Then
'reinsert identity property
adoSQLCommand.CommandText = "SET IDENTITY_INSERT " & stTableName & " OFF"
adoSQLCommand.Execute
End If
Set adoCommand = Nothing
Set adoSQLCommand = Nothing
End Sub
Who convert access dba to ms sql 2000 standard.
I try with access conversion, but some buttons commands and rules don´t work.
The dba contains tabs, forms, rel´s, macros, modules and relations with tabs and examination images. It´s possible convert everything without damage or transform this itens?
The dba contains critical information about identification and personal clinic story about patients, but the access capacity it´s out (2GB) and i need to expand this dba.
I have some urgency to this problem, it´s depends to buy a windows 2008 server and mssql server 2008.
I hope everyone help me.
Thanks,
hi all,
How to convert a database from MS Access to MS SQL, is there some tool like that?
Hello does anyone know how to convert this access code to sql. Also is there a program out there that will do it for you.
IIf([Ceridian]![UnionIndicator]="U" And [YearsService]>=3,[HealthBenefits]![medplan.Bi-WeeklyURBio-Rad]*26,[HealthBenefits]![medplan.Bi-WeeklyBio-Rad]*26) AS MedicalCostER, IIf([Ceridian]![UnionIndicator]="U" And [YearsService]>=3,[medplan]![Bi-WeeklyUREE]*26,[medplan]![Bi-WeeklyEE]*26) AS MEdicalCostEE
I have an Access database and would like to convert it to an MS SQL database?!
View 4 Replies View RelatedI am being asked to convert this access query into sql server 2000.
Access query
SELECT Left(Trim([Notes_Primary_Key]),InStr(Trim([Notes_Primary_Key])," ")) AS PcnPID, Trim([Notes_Secondary_Key]) AS PcnTicketNum
FROM tri_offnotes;
I'm new to SQL and am not familiar with which function replaces the InStr access function.
I have an Access database that used to produce a mass of Performance Indicators from Access tables. The data is now held on SQL Server and I run the Access queries from the SQL tables. I wouldlike to move all the queries over to SQL but not sure if I can do that. Here's an example of one of the queries (the SQL view)
SELECT tblCalls.*
FROM tblCalls
WHERE (((tblCalls.Call_date)>=[Forms]![ReportParams]![SDate] And (tblCalls.Call_date)<=[Forms]![ReportParams]![EDate]) AND ((tblCalls.NotAccepted)=False) AND ((tblCalls.Completed_time) Is Not Null) AND ((tblCalls.Category)="fly tipping"));
SELECT Month([Call_date]) AS Mnth, DateSerial(Year([Call_date]),Int((Month([Call_date])-1)/3)*3+4,0) AS Qtr, Sum(Work_Days([Call_date],[Completed_time])) AS RespTime, Sum(1) AS Count, Sum(Work_Days([Call_date],[Completed_time]))/[Count] AS AvgTime
FROM qryFlyTippingStatsSummary1
GROUP BY Month([Call_date]), DateSerial(Year([Call_date]),Int((Month([Call_date])-1)/3)*3+4,0)
ORDER BY Month([Call_date]), DateSerial(Year([Call_date]),Int((Month([Call_date])-1)/3)*3+4,0);
what's the best way in sql 2005 to convert a access db?
View 1 Replies View RelatedCan I convert .MDB file to SQL Server database or I need to develop SQLserver database from begin, every table, every field?
View 2 Replies View RelatedI'm going crazy trying to convert an Access Function to SQL.From what I've read, it has to be done as a stored procedure.I'm trying to take a field that is "minutes.seconds" and convert it to minutes.This is what I have in Access:Function ConvertToTime (myAnswer As Variant)Dim myMinutesmyMinutes-(((((myAnswer * 100)Mod 100/100/0.6)+(CInt(myAnswer-0.4))))ConvertToTime =(myMinutes)End FunctionWhen I tried to modify it in SQL:CREATE PROCEDURE [OWNER].[PROCEDURE NAME] AS ConvertToTimeFunction ConvertToTime(myAnswer As Variant)Dim myMinutesmyMinutes = (((((myAnswer * 100)Mod 100)/100/0.6)+9CInt(myAnswer-0.4))))ConvertToTime=(myMinutes)EndI get an error after ConverToTime.
View 2 Replies View RelatedHello to everybody,
I had just installed SQLserver Express. I'd like to know if tool to migrate an Access db to SQLexpress exists
Thank you
First off, I apologise if this is classed as off topic as it's concerning access but I couldn't see another forum that was better suited for this question.
I'm trying to run this query in Access. I designed it in SQL Management Studio with a test database that I set up and it runs fine. However, when I tried to run it in access I get a syntax error on the subquery bit. Can anyone tell me what I'm doing wrong here?
UPDATE tblInvoice
SET tblInvoice.PeriodID =
(SELECT p.PeriodID FROM tblPeriod p
INNER JOIN
tblInvoice i
ON i.TransDate BETWEEN p.PeriodStartDate AND p.PeriodEndDate
WHERE i.InvoiceID = tblInvoice.InvoiceID)
Thanks in advance,
Andy.
how to convert this access query to sql query
IIf([Total Bunches] > 0, Production * 1000 / [Total Bunches], 0) as Name2
SUM(IIf(BlockInYield = -1, [SIZE], 0)) as Name1
IIf(BlockInYield = TRUE, IIf(TC_M > 0, TC_M, TC_DENS *[SIZE]), 0) as Name
please......
how To Convert .btr file in SQL OR ACCESS
please help me
tanks
Is there anyway to convert an SQL Express database to SQL server or Access format?
View 1 Replies View RelatedI have a sql server database and i need to convert to Access, how can i do this using sql server?
View 4 Replies View RelatedSorry if this has been answered before. Could not find any answers.
OS Vista Business, SQL Server 2005 Express.
I have a CSV file which I imported to Access only 100 records with 8 fields. Then ran Tools >> Database Utilities >> Upsizing Wisard. The result was I exported the table field headers but not the data. Is there any method on how I can get the field headers and data into SQL Server Express?
I am trying to convert code I have working for access to work with SQL.
fldName, fldEmail, ID are the names in the database. recNum does have the value of the record that I want to edit. Here is the error I am getting.
System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '?'.
And here is the stack trace (which I don’t know how to read except for the line the error is on)
[SqlException: Line 1: Incorrect syntax near '?'.]
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +723
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +194
goodellweb.adm_contact.editNow_Click(Object sender, EventArgs e) in C:Inetpubwwwrootwebrootgoodellwebadmadm_contacts.aspx.vb:306
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain() +1315
here is my code.
Dim editSQL As String = "Update tbEmail Set fldName=?, fldEmail=? Where ID=?"
Dim SqlConn As New SqlConnection(ConnStr)
Dim Cmd As New SqlCommand(editSQL, SqlConn)
Cmd.Parameters.Add(New SqlParameter("@fldName", nameEdit.Text))
Cmd.Parameters.Add(New SqlParameter("@fldEmail", emailEdit.Text))
Cmd.Parameters.Add(New SqlParameter("@recNum", recNum))
SqlConn.Open()
Try
Cmd.ExecuteNonQuery()
Finally
SqlConn.Close()
End Try
Response.Write("recNum " & recNum & " <br>")
Thanks
Michael
Hello sir,
I send you this message to told hwo can i do to convert one database which is in Access97 to SQL Server6.5.
I must create a interface in the web which is able to interrogate the database that the reason why i want to do that.
Thanks for any help in advance.
I have an Access application with various DB's linked together. One of the DB's contains a field, SOURCECODE, which was mistakenly entered in as an nvarchar(4000) field in MS Sql Server. When I link the table, Access converts it to a memo field.
There will never be more than 10 chars in that field. The company that created the DB says its will be too risky to change the data type. I need to link this field to another field that's a VARCHAR.
How can I do this? Access doesn't allow the CAST feature.
I have used the Upsizing Wizard in Access 2003 to convert a database to SQL. When I open it using SQL 2000 Desktop edition, I am finding a problem with the views and stored procedures.
When I open a query in design view, the tables are displayed in the upper pane without the details of the individual fields. All I get is a tick box next to *(All Columns)
If I create a new table, the same thing happens. If I create a new database, then make a table within it, any queries behave normally, with all the fields displayed.
I would be grateful for any ideas
is it possible to convert an Access database to SQL Server 2005 or 2000?
View 3 Replies View RelatedI am trying to convert a code from access Db to sql code?
II(Left([dbo_ClaimLosstype].[dscr],4)="Liab","D_Liab","D_Property"),
ive been having a lot of trouble converting this last line from my access database to sql server. i was hoping someone could help
order by Name, IIF(Risk.Calc like H*, 1, IIF (Risk.Calc like M*,2,3)), Risk.Chance*risk.cons DESC, risk.title
any suggestions?
Here is the access query
SELECT tbl1.evenID, Sum(IIf(IsNull([evpeDateCompleted]),0,1)) AS Completions, Count(tbl2.evpeID) AS Attendees, IIf(Count([evpeID])=0,"No Bookings",IIf(Count([evpeID])=Sum(IIf(IsNull([evpeDateCompleted]),0,1)),"Closed",IIf(Sum(IIf(IsNull([evpeDateCompleted]),0,1))>0,"Closing","In Progress"))) AS Status
FROM tbl1 LEFT JOIN tbl2 ON tbl1.evenID=tbl2.evpeEvenID
GROUP BY tbl1.evenID;
coding is my passion
I am converting some access to SQL at work and I am having a problem with a query that uses the access FIRST function
My query looks like this in access
SELECT
TBL1.ID
, FIRST(TBL1.Code) AS FirstCode
FROM
TBL1
WHERE
TBL1.Marker = 'X'
AND Left(ID,1) IN('A','B','C','D','E')
GROUP BY
ID
I have tried to convert it using a slightly different method. So instead of picking the first value of the Code field that appears in the select, (which is what access would do and SQL can’t do as far as I’m aware). I first select the ID and code with the highest value associated with them. The below script is what I did. I would’ve thought they would produce the same number of records but they don’t.
SELECT
ID
, Code FirstCode
FROM
(
SELECT
ID
, Code
, MAX(Value) MaxValue
FROM
TBL1
WHERE
TBL1.Marker = 'X'
AND LEFT(ID,1) IN('A','B','C','D','E')
GROUP BY
ID
, Code
) T1
I having trouble converting this access query to sql 2000, can anyone offer advice? The where clause is troubling me.
---Access Query---
SELECT DISTINCT Procedures.PatientNumber,
rocedures.TicketNumber,
Procedures.Counter, Procedures.DateOfService, Policies.PatientNumber, Policies.CarrierCode,
Policies.EffectiveDate, Policies.Employer, Policies.FirstName, Policies.GroupNumber, Policies.LastName,
Policies.MiddleInitial, Policies.PolicyHolderBirthdate, Policies.PolicyNumber,
Policies.PrimaryPolicyIndicator, Policies.TerminationDate, Procedures.ProcedureCode,
Procedures.PrimaryPolicyCarrierCode
FROM Procedures LEFT JOIN Policies ON (Procedures.Counter = Policies.Counter)
AND (Procedures.PrimaryPolicyCarrierCode = Policies.CarrierCode)
AND (Procedures.PatientNumber = Policies.PatientNumber)
WHERE (((Procedures.DateOfService)>=[effectivedate]
And (Procedures.DateOfService)
<=IIf(IsNull([terminationdate]),Now(),[terminationdate])))
OR (((IsNull([procedures].[primarypolicycarriercode]))<>False))
I got a big Access file (1 400 tables) to convert to SQL and I wouldlike to be provided with some automated tools, except upsizing wizardand DTS, to convert it on my own.I got a lot of forms and query to convert too.Can someday provide me with at least one tool name ?Thanks,Guy
View 12 Replies View RelatedHello!
Somebody knows how to convert the data from Clarion (tps) to mdb or mdf files?
Any variants?
Thanks to all!
I use Access 2007 SQLserver Upsize but get errors saying SQL Server not found. Is there another way of copying tables from Access to SQL Server 2005 Express.
I am not very familiar with SQL Server Express but did a course in SQL Server programming.
hi,
I'm using this query to generate a report. When I preview the report I should be able to select values from parameter drop down list for the following fields.
OfficerName
OfficerId,
BorrowerName
frmMain( is access form type. wonder how do I get this as a parameter to my ssrs report. how do I make available data set for this one?. I can not see available data set for this one when i click drop down menu in report parameter box)
Forms
ToDate
FromDate
But when I run the query I can see only date parameters. I can not see anyotehr parameters. I can add parameter and select fields like officerName, OfficerId etc. But it does not work when i preview it.
Actually one of the developers has created a stored proceedure for this report. He already created an Access report, What I really want to do is recreate it in SSRS or convert into SSRS. But in either way, this parameter thing does not work.
Here is the code;
Code Snippet
CREATE PROCEDURE [dbo].[GET_EXPENSES_REIMB_OFFICER]
(@FromDate DateTime, @ToDate DateTime)
AS
SELECT
BorrowerName,
TExpense,
TReimbursement,
CREATE_BY_DT,
OFFICER_ID,
RC_NUM,
OfficerName,
FROM (
SELECT
b.Title AS BorrowerName,
m.INVOICE_TOTAL_AMT AS TExpense,
0 AS TReimbursement,
CONVERT(varchar, m.EFFECTIVE_DT, 101) as CREATE_BY_DT,
m.OFFICER_ID,
m.RC_NUM,
o.OfficerName,
FROM EXPENSE m
INNER JOIN .adBorrowerObligation bo on m.Borrower_Obligation_ID = bo.BorrowerObligationID
INNER JOIN .entity_to_obligation eto on bo.BorrowerObligationID = eto.Borrower_Obligation_ID and eto.entity_type_id = 1
INNER JOIN .adEntity b on eto.Entity_ID = b.EntityID
LEFT OUTER JOIN OFFICER o ON m.OFFICER_ID = o.Enum
WHERE (m.EFFECTIVE_DT >= @FromDate)
AND (m.EFFECTIVE_DT <= @ToDate)
UNION ALL
SELECT
b.Title AS BorrowerName,
0 AS TExpense,
r.REIMBURSEMENT_AMT AS TReimbursement,
CONVERT(varchar, r.CREATE_BY_DT, 101)as CREATE_BY_DT,
r.LENDER_ID as OFFICER_ID,
r.RC_NUM,
o.OfficerName,
FROM REIMBURSEMENT r
INNER JOIN adBorrowerObligation bo on r.Borrower_Obligation_ID = bo.BorrowerObligationID
INNER JOIN entity_to_obligation eto on bo.BorrowerObligationID = eto.Borrower_Obligation_ID and eto.entity_type_id = 1
INNER JOIN adEntity b on eto.Entity_ID = b.EntityID
LEFT OUTER JOIN OFFICER o ON r.Lender_ID = o.Enum
WHERE (r.CREATE_BY_DT >= @FromDate)
AND (r.CREATE_BY_DT <= @ToDate)
)
ad
ORDER BY OFFICERNAME DESC, CREATE_BY_DT, BorrowerName
can anyone help me out to design this report?
Thanks in advance