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

 

[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!!!

 

[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.