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
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
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>
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....
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.
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 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);
I'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.
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)
Sorry 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
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
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;
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 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
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.
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.
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