Displayin SQL resultset in ms access

Question

0.00/5 (No votes)

See more:

Hi.

I have created a form in ms access.. that is to search in sql table and retrieve data based on the search key word..
Upto this i have done but the main thing ...

displaying the retrieve data in ms access.. no idea to do this please anyone help me to do this.

Thanks

Posted 21-Oct-11 3:05am

sathish nagulan

Add a Solution

Comments

Bala Selvanayagam 23-Oct-11 17:21pm

What you have done so far and where you are stuck ?

What version of MS Access ?

sathish nagulan 24-Oct-11 2:58am

I have done up to executing sql query ..the same i did in sql and the output will display in a spread sheet ..the same in access i have to display the output..

I am using access 2003

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Slacker007 · Answer 1 · 2011-10-27T00:40:00

Solution 1

The easiest way is to link your SQL Server tables to your Access project via ODBC. There will be a few problems intially because SQL Server and Access use slightly different data types but a few quick searches on MSDN should point you in the right direction.

To setup an ODBC connection, navigate to:

Control Panel --> Administrative Tools --> Data Sources (ODBC) and click on the system tab.

If you have not done this before then I highly recommend that you look it up on Google and you will be pointed in the right direction.

Hope this helped you in some way. Let me know if you have any other questions.

-- Cheers

Posted 27-Oct-11 0:40am

Slacker007

Comments

sathish nagulan 28-Oct-11 6:42am

Hi..

I have connected to sql...my question is im running a sql query in access VBA code that will retrieve some result that result i want to show in access page..like excel will display the result set in spread sheet the same how to do in access.

Slacker007 28-Oct-11 6:50am

When you say "access page" are you referring to a form or the old web-style pages Access use to have.

sathish nagulan 28-Oct-11 7:05am

yes i am using a form.. that is to retrieve data from sql table based on the parameter passed through the form.

Slacker007 28-Oct-11 7:15am

Have you tried passing your query to a recordset object and then conecting your UI control to the recordset? i.e. MyTextbox.Value = MyRecordest!MyFieldName.

sathish nagulan 28-Oct-11 7:22am

No i have not done that..will paste my code kindly gice me solution

Dim myconn As ADODB.Connection
Dim rec As ADODB.Recordset

Set rec = New ADODB.Recordset
Set myconn = New ADODB.Connection
strCS = "Provider=SQLOLEDB;" & _
"Data Source=server;" & _
"Uid=sa;" & _
"Pwd=pass;" & _
"trusted connection = yes;"
myconn.ConnectionString = strCS
myconn.Open strCS

With rec
.ActiveConnection = myconn
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
'.LockType = adLocOptimistic
.Source = "SELECT * FROM [" & DBName.Value & "].[dbo].[" & Tbname.Value & "]" & vbCr & _
"WHERE [" & DBName.Value & "].[dbo].[" & Tbname.Value & "].[manufacturer no] LIKE '%" & prno.Value & "%'"

.Open

End With
rec.Close
Set rec = Nothing
myconn.Close
Set myconn = Nothing
End Sub

Slacker007 28-Oct-11 7:25am

I have bookmarked your question and will help you with this when I can tonight or tomorrow morning. I actually don't have the time right now because I am at work and things here are picking up.

sathish nagulan 28-Oct-11 7:28am

Its okay...help me when u r free...

Thank you so much.

sathish nagulan 9-Nov-11 2:11am

Hi...you got solution for my question????

Slacker007 9-Nov-11 5:54am

I am very sorry for not getting back to you on this, sathish. Thank you for reminding me. I have sent the link to my email account so that I can go over it at home.

sathish nagulan 15-Nov-11 7:22am

Hi...you got solution???