How can I display data from SQL

  • Thread starter Thread starter METIN GUCER via AccessMonster.com
  • Start date Start date
M

METIN GUCER via AccessMonster.com

I have an uncommon problem. In my company, we are using accounting program
and back end is SQL. That programs doesn't give us the information on the
same page. My users want me to show the information what they need on the
same page. I know how to connect Ms Access with SQL server and pull the
information that I need.My problem is, not know how to show it on a
datagrid form. What I did was create a table and update the information
from SQL server but everytime when they run the code, access database gets
bigger and bigger. Please help...I need to know how to display information
on form or datasheet (datagrid)
with the results?

Thanks.
 
Hi,

Seems a long way round things, but if what you are doing is clearing
the table, then importing loads of records, then viewing them, then at
some point clearing the table... The newsgroup you have posted in is
for ADP projects, if you are in the right group then your project is an
ADP project which has no understanding of local tables because it uses
a direct connection to a SQL server.

If you are using an access MDB the process of
deleting/importing/deleting/importing will bloat the database and will
need to be compacted as frequently as the import procedure to keep it
an good size.

I hope I have answered your query.

Regards

Alex
 
Thanks for answering my question right away,
As I mention that I don't want to import or export records. I just want to
use the mdb as front end and SQL back end. This is the code that I was
using but my form doesn't connect with sql server directly and gives me an
error message. "Me.RecordSource = STRSQL" on this line.
should I use DAO instead of ADO. if yes then then how I am going to to it?

Dim cnn As ADODB.Connection

Dim STRSQL As String
Set cnn = New ADODB.Connection
STRSQL = "SELECT STOK_KODU,TOP_HAR FROM STOKHAREKETLERI" Dim rstEmployees
As ADODB.Recordset

cnn.Open "Provider=sqloledb;" & _
"Data Source=blablabla;" & _
"Initial Catalog=blablabla;" & _
"User Id=SA;" & _
"Password=;Network Library=dbmssocn"



Set rstEmployees = New ADODB.Recordset

rstEmployees.Open STRSQL, cnn, adOpenKeyset, adLockOptimistic

Me.RecordSource = STRSQL
 
Hi Metin,

if you link you SQL tables into access directly via the link table
function, e.g. click on file, get external data, link tables then once
this is setup you should be able to reduce your code down to

Dim STRSQL As String
STRSQL = "SELECT STOK_KODU,TOP_HAR FROM STOKHAREKETLERI"
Me.RecordSource = STRSQL
me.refresh

because access will look after the connection stuff for you.

if you are using an MDB it is using DAO be default data access layer,
if you are using an ADP project then ADO is the default data access
layer.

I hope that answers your question.

Regards

Alex
 
Hi Alex,
I have done what you asked me to do. I linked the table from sql into my
access database. Now, I have a problem.

When I open the database and trying to open the linked table, it asks for a
password to connect. I know that if I made the connection with the code. I
was be able to put the password into the code so user wouldn't have to know
nothing.
I am using mdb database so how to code it to connect my access to sql
server table instead of using access directly via the link table
function.

Thank you very much for your help...

Metin.
 
Hi Metin,

The only way to make this work properly is to link your table via the
link table procedure, if you use the link table procedure make sure
that you are ticking the save password box bottom right hand corner of
the dialog box, this should save your password into access.

A couple of questions for you.

What version of SQL are you running?

What security system are you using, e.g. SQL and windows or windows
only?

Also check your ODBC drivers are upto date, download the latest MDAC
from microsoft from http://www.microsoft.com/data

The idea of linking forms via code to external data that is not linked
into the database does not work as far as I know.

post back here if you are still having problems.

Regards

Alex
 
Back
Top