Wrox Programmer Forums
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA 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 July 7th, 2015, 09:33 AM
Registered User
Join Date: Jul 2015
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Help with a piece of code

Hi guys, I have this code here:

Option Explicit

Const ConStrSQL As String = "Provider=SQLNCLI10;Server=MYSERVER;Database=MYDATABASE;Trusted_Connection=yes;"

Public Sub CopyDataFromDatabase()

Dim con As ADODB.Connection
Set con = New ADODB.Connection
Dim conData As ADODB.Recordset
Set conData = New ADODB.Recordset
Dim i As Integer
i = 2
con.ConnectionString = ConStrSQL
With conData
.ActiveConnection = con       
Do While Sheet1.Cells(i, 3).Value <> ""
.Source = " SELECT distinct left(ut.Text,27) " & _
" FROM [MYDATABASE].[dbo].[UnitText] ut, dbo.unit u " & _
" WHERE ut.unitid in (select unitid from dbo.unit where serialno like '" & CStr(Right(Sheet1.Cells(i, 3), 8)) & "' ) " & _
" AND ut.text like 'EPP:%' " & _
" AND u.unitid = ut.unitid " & _
" ORDER BY 1 "
i = i + 1
Sheet1.Cells(i, 4).CopyFromRecordset conData        
End With

End Sub
My problem is that it should appear in the Sheet1 all 22 values that it searches for but it is actually only showing the last result. I have a guess the problem is in the .copyRecordSet part from what I read on the internet but I am newbie in VBA (started learning last Wednesday) and couldn't figure it out yet (almost 2 days going nuts). All the help is welcome :)

Similar Threads
Thread Thread Starter Forum Replies Last Post
Question about a piece of code chobo2 C# 2008 aka C# 3.0 8 November 30th, 2008 11:53 AM
How to delete a piece of code Charlie05 C# 2005 2 August 3rd, 2008 11:32 AM
This piece of code is giving an error sourik C# 2 July 15th, 2006 09:18 AM
This piece of code is giving an error sourik Classic ASP Databases 1 July 14th, 2006 09:10 PM
What does this piece of code do? mito_lal Oracle 1 July 11th, 2006 06:44 AM

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