Never worked with SQL dumps before, but I have worked with other DBMS dumps. (We're an ORACLE shop, mostly, but we used to also have a wierd box called a ShareBase, which was an SQL back-end server before ORACLE became quite so popular.)
Usually, DB dumps come in one of two flavors: Fixed-width or constant delimiter. You need to know which one you have 'cause you can't proceed without that knowledge. It is rare verging on impossible to correctly handle a case that is neither of these two dump formats.
Basically, this is going to require an IMPORT operation where you point Access as the dump file, which is in some text format. Then you can tell Access which type of file it is (fixed-width or constant-delimiter). You can adjust the field widths to define each field if it is a fixed-width case, or you can define the delimiter if it is a delimiter case.
After that, you import your data to a new table. Certain cases might give you trouble in terms of data translation. Particularly, text-oriented dates or fields with mixed numeric and alphameric where the first few records are all numeric but later records in the file have alphamerics for the field. However, the import process allows you to override the wizard's guess at the correct data type. The import wizard will also give you the chance to define the table's primary key at this time. Unless you want Access to build an
autonumber prime key for you, I would tell it "no key" for now.
After you have done the import, if you need to adjust field sizes or storage methods, you can enter design mode on that table. The most common change you will need to make is to shorten text fields because there is a default width for ALL text fields imported that way. (If you have not changed the default under Tools >> Options and then the Tables/Queries tab, Table Design area, it is probably 255 bytes per field.)
Note that if two fields in two tables in your data source are prime-key and foreign-key in a relationship, you must always choose them to have the same size in the final versions of your tables. So keep that in mind when adjusting text-field sizes. Same is true for numeric keys.
Before you are finished with each file, you might also wish to define keys. At this point, you have all file data imported. If you now attempt to define a prime key for the table and something is wrong, you will not lose data 'cause the key definition will fail instead. The message might tell you that duplicates were detected (most likely case for a failed prime key) or that some fields were empty/null (another likely case). You will have a chance to identify the fields causing the problem, fix them, and try again. HINT: You don't need to have a key defined in order to sort a table when it is in DATASHEET view, so you can find the nulls quickly. The duplicates will take a bit of work, but there are query wizards for identifying duplicate entries in a table, so you might want to look in the query wizard list. Or just write a summation query that counts the number of times each possible value occurs in your chosen key field. Lots of ways exist to skin that particular cat (MMMMMEEEEEEEOOOOOOWWWWRRRRRRR

)
Do the above once for each file to be imported. When done, you should be ready to establish relationships and start building other features like queries, reports, forms, etc.