Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 17th, 2007, 10:08 AM
Friend of Wrox
 
Join Date: Jan 2007
Posts: 109
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to scandalous Send a message via MSN to scandalous
Default Linked tables "run time error 3622"

I am getting an error:

Run Time Error 3622

"You must use the dbseechanges option with
openrecordset when accessing a sql server
table that has an identity column."

This is happening after i upsized my Tables to SQL 2005 (different machine) and linked the tables back to my MDB. I am thinking that i need to declare somewhere a connection but not sure where.

I found this Post

However, i am not sure how that would be applicable to my situation. Below is the code and the Red text is where it errors out.


Code:
--------------------------------------------------------------------------------


Public Function fnCreateJobs()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim md As String 'Model
Dim qi As String 'QuoteID
Dim yr As String ' Year
Dim un As String 'Units
Dim jn As String 'Job Number
Dim jn1 As String
Dim num As Integer
Dim counter As Integer

Set db = CurrentDb()
Set rs = db.OpenRecordset("JobNumberSummary")
md = Me.txtModel.Value
qi = Me.txtquoteid
yr = Me.txtYear.Value
un = Me.txtOpenArgsUnits.Value
qu = Me.txtQuoteNumber.Value
jn = Me.txtJobNumber.Value
'the loop will start a counter from one to the number of units(un)
For counter = 1 To un
'we check how many records are in the qry qryNumberOfQuotes,
'we know there zero so we add a one, to start increments at 1
num = DCount("QuoteNumber", "qryNumberOfQuotes") + 1

'Debug.Print num
'concat increment to job number
jn1 = jn & num
' add records or write the records to the table named tblJobNumbers
With rs
    .AddNew
    !ModelID = md
    !QuoteID = qi
    !YearID = yr
    !QuoteNumber = qu
    !JobNumber = jn1
    .Update

End With

Next counter

End Function
--------------------------------------------------------------------------------


 How do i know if i have declared the right source when linking tables?


 
Old April 17th, 2007, 10:34 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I am not sure what you are doing here, but I see DAO. You cannot use DAO with SQL Server (any version) since it is only for Jet databases. You have to use ADO.

Do you need more help?



mmcdonal
 
Old April 18th, 2007, 06:50 AM
Friend of Wrox
 
Join Date: Jan 2007
Posts: 109
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to scandalous Send a message via MSN to scandalous
Default

So basically i need to switch out my DAO for something like this?

Dim cn As New ADODB.connection
Dim rs As New ADODB.recordset
Set cn As CurrentProject.Connection

rs.open "JobNumberSummary", cn, adOpenKeyset, adLockOptimistic, adCmdTable

Actually i have another question! can i make the connection once and refer to it like how we do with modules? i mean because the connection is the same anyway isnt it?

then use the rest of the existing code?- or do i need to change it I am trying to find a site that explains the primary differences of DAO and ADO but as you already know I am extremely slow.

On a seperate note. this is part of my quote creation code.
 
Old April 19th, 2007, 06:32 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

You don't have to declare the connection if the tables are linked. In that case, do this:


Dim rs As New ADODB.recordset
Dim sSQL As String

sSQL = 'SQL for "JobNumberSummary",
Set rs = New ADODB.Recordset

rs.open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

I don't think you want to open a connection and then leave it there, but it depends on how many users you have. There might be a lot of connections to the SQL Server. Better to close it after each Sub. But this way sort of gets rid of the issue. You have no cn.Close statement at the end of your Sub.

mmcdonal
 
Old April 19th, 2007, 09:07 AM
Friend of Wrox
 
Join Date: Jan 2007
Posts: 109
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to scandalous Send a message via MSN to scandalous
Default

Sorry, I know I can be difficult.
 'SQL for "JobNumberSummary",
your talking about the connection string right?

which in my case would be

Provider=SQLOLEDB.1;Persist Security Info=False;User ID=antonio;Initial Catalog=AutoportSQL;Data Source=WINSERV


thanks!


 
Old April 19th, 2007, 09:56 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

The SQL String. Like "SELECT * FROM User WHERE [Clue] >0"

mmcdonal





Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert Query Error & Run-Time Error 3022 DavidWE Access 1 July 31st, 2008 11:17 AM
run-time error(s) Chacko C++ Programming 0 March 4th, 2007 02:28 PM
run time error ashishroyk Java GUI 0 October 8th, 2004 01:42 AM
Run Time Error JBond Access VBA 0 May 27th, 2004 09:50 AM
RUN-TIME ERROR compcad Beginning VB 6 2 May 21st, 2004 02:01 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.