Need tips on importing SQL table into Access (VBA or Macro)

[gilinnc]

I am hitting a brick wall. I don't do VBA, but I will if I have to.

I need tips on using Access 2003 to import records from a SQL table on a server, but instead of straight import (over 300k of records), I want to be able to select records based on dates. For example, for current year.

Thank you all for helping me out.

Gil

 

[Pat Hartman]

Link to the table. You can then create queries that select the records you want. Once the select queries work, you can turn them into make-table queries to import just the selected rows.

 

[alex2051]

plz about the same problem it goes like this .
I want to make a field user field password and a button that will confirm if the user matches the password
the data comes from a table of users witch contains the username and password.
plzhow do i reffer to the table fields and confirm what i need plz help me and true help not riddles thank a lot!!!

 

[Pat Hartman]

Welcome Aboard
Please start a new thread when asking a question.

You can validate a password by using DLookup()

If DLookup("PasswordField", "tblUsers", "UserLogin = '" & Me.txtUserLogin & "'") = Me.txtPasswordField Then
    'valid password and userid
Else
    Msgbox "Your userid or password is invalid.  Please try again.",vbOKOnly
    Exit Sub
End If

 

[alex2051]

thanks man finally something that looks right but still i have an error

Private Sub Command7_Click()

If DLookup("Password", "WorkerT", "WorkerNumber = '" & Me.UserName & "'") = Me.Password Then
    MsgBox "good"
Else
    MsgBox "Your userid or password is invalid.  Please try again.", vbOKOnly
    Exit Sub
End If


End Sub

Password-field in tavle
WorkerT-table
WorkerNumber-field in table
UserName-txtbox username
Password-txtbox password
the error is:
runtime error '3464' Data type mismatch in criteiria expression
please help and thanks so far
and one more thing what thoes this command do "dlookup" ?

 

[gilinnc]

Pat -

I know I can link to the table. But what is preventing me to do this is the table is updated many times throughout the day and I was told that if I link the Access to the sql table, it will cause the program to freeze/lockup because it will lock a record.

We have over 30 people using the system table and I would like to do a quick import using the where clause to select a specific record range.

If this can be done using transferdatabase or using VBA, I would greatly appreciate any tips. I will be using the ODBC connection engine to pull the records.

Again, thank you all for your help.

Gil

 

[mdlueck]

I need tips on using Access 2003 to import records from a SQL table on a server, but instead of straight import (over 300k of records), I want to be able to select records based on dates. For example, for current year.

I would suggest using a nested DAO.QueryDef solution which would download records from the SQL back end into an Access table without requiring VBA code to loop/process each record.

Example of DAO.QueryDef objects downloading records from a SQL BE DB via Pass-Through query and populating a FE temp table with them
http://www.access-programmers.co.uk/forums/showthread.php?p=1119605&posted=1#post1119605

You may modify the SQL of the query being passed to the SQL BE DB to specify the date range you are interested in. The outer wrapper query still would be selecting everything from the inner pass-through query.

 

[Pat Hartman]

Pat -

I know I can link to the table. But what is preventing me to do this is the table is updated many times throughout the day and I was told that if I link the Access to the sql table, it will cause the program to freeze/lockup because it will lock a record.

You were misinformed. Most of my Access applications link to SQL Server tables and they don't have this issue.

In an Access form that is bound to a query of a Jet or ACE table, editing a record is immediately recognized by other applications that are accessing the same record. That is not the case when the query gets its data from an ODBC data source. In this case, the update is asynchronous rather than synchronous. That means that the change is not even sent to the server until you have completed it and done something to make Access recognize that the record needs saving. At that time, Access sends an update query to SQL Server asking it to perform the update. So, I'm not sure what your "source" is talking about. Access works very differently with linked ODBC tables than it does with native Jet/ACE tables (local or linked).

You could run into conflicts if there are long running batch updates that execute during the day when people are actively changing records. Someone attempting to make a change to a linked table via a form could get a message saying that the record cannot be updated at this time. But you would get that message whether you used linked tables and a bound form or an unbound form with stored procedures.